data sent from backend file (Access 2002)

Topic

New Reply

There is a lot of discussion by the network group where I work stating that when you have a split database Access creates a lot of network traffic in terms of the amount of data sent across the network as opposed to other database products, there is a project to move the backend tables to SQL Server which will improve performance on the user side, but does it change the amount of data sent across the network? I understand that you can use functions with SQL Server to improve the amount of data sent with existing Access front ends, but is the amount of data sent significant enough to justify moving all of the front ends to .NET? The databases are small in terms of user numbers and in terms of data, no huge amounts of anyting. My personal experience is that Access front ends coupled with SQL Server back ends is a nice solution for smaller databases, but I don’t know how Access sends data to client front ends from .mdb backends or SQL or how other products might work to justify my position with the network people. I have not considered using .adp front ends I have not had any experience with them and Microsoft is now recommending not to use them. Any thoughts or experience would be appreciated.

Reply | Quote

Replies

From my limited understanding when you have a Access backend there is a high amount of network traffic as all processing is done in the front end.

With SQL server as the backend the amount of data going across the network is minimal since the back end is doing the processing before it sends just the data it needs to back to the front end.

Again microsoft seems to recommend an Acces front end for SQL server backends. I have heard it takes a lot longer to develop .net front end than it does in Access.

As I said, this is mainly what i have heard.

Reply | Quote

I have heard the same, and am looking for some type of confirmation. It does takes a lot longer to develop a .NET front end, and to redevelop functioning systems seems to me to be an unnecesary effort. My recommendation has been just to move the back ends to SQL Server.

Reply | Quote

Simply migrating the backend to SQL Server will not necessarily diminish network traffic, since the Access frontend will still be doing most of the data processing. To let SQL Server do the hard work, you need to convert your queries to pass-through queries, or define views in the SQL Server database.

For example, let’s say that you have a table with 100,000 records, and you have a query that uses selection criteria to return 100 of those records (to be displayed on a form or report).

With a normal query, Access will sequentially transfer all 100,000 records across the network into the memory of the PC running the query, and discard the 99,900 records that don’t meet the criteria.

With a pass-through query, SQL Server will apply the criteria, and send only the 100 records that meet the criteria across the network to the PC running the frontend – an enormous reduction of networtk traffic.

See:
How to create an SQL pass-through query in Access
Microsoft Access Pass-Through Queries to SQL Server
ACC: How to Simulate Parameters in an SQL Pass-Through Query

Reply | Quote

I’ve had alot of experience working with Access using an SQL backend, and the answer is “it depends”.

As has been stated, under normal circumstances Access does all its processing in the frontend. However, this does not mean that if you have a 100,000 record table that every time you query this table all 100,000 records come across the network to the frontend. Access can use the indexes to reduce the # of records based on the selection criteria. This happens regardless of the backend.

However, using pass-through queries with an SQL Server backend will DRAMATICALLY improve performance. In these situations, all the processing is done by the SQL Server (which is much faster) and only the results come back across the network (reducing network traffic).

However, you can’t use pass-through queries for everything; one primary reason being that they are not updatable. They are great for filling listboxes and in reports Therefore, you have to adopt preventive measures to minimize the need for Access to work with large recordsets that you need to update. For example, let’s say you have the usual Customer form. Many such forms are bound to a table so Access always has to pull-down all the records in the table. Instead, you can create a recordset with only a single record! (See Create Powerful Bound Forms ) Another thing to do is only populate listboxes and subforms when they are needed. For example, you may have that Customer form with several tab pages, one of which contains a Transaction History subform. Don’t load that subform immediately, only do it when the tab page is displayed. And don’t use the normal Master/Child properties. This will requery the subform every time another customer is displayed (even if the tab page isn’t being displayed). Put the selection criteria in the subform’s recordset query, then manually requery the subform whenever the tabpage is selected.

Reply | Quote