What goes over the line when running a query?

[Taaner]

Hey,

we use a lot of Access VBA applications with backend shared on a network drive and distributed frontend on the PC of the users. And we have some performance issues, mostly when users are not on the HQ LAN but on in a regional office over a WAN or from home by VPN.

I try to optimize the code of this applications (actually the application and the database should be redesigned but that is not an option now) but I don't know what data is going over the network when a query is launched. A lot of people here tell their own story, but I haven't found anyone who can explain me in detail how Access technically handles a query.
Does the application first reads the index(es) from the backend, and then only the data that meets the selection? Or is the full table read to the PC to make the selection? Are the indexes read once at the moment it makes the connection with the backend, or every time you do a selection?

I searched for more information about this issue but haven't found anything helpfull yet. So does somebody can explain some things, or does anyone know a good article about this?

[zmbd]

In VBA it can depend on how you open the record set AND on how the tables are setup.

The basic concept (and someone correct me if I have this wrong - PLEASE :) )
If the table is indexed and you run a basic stored query, the front end first checks for the availability of and INDEX on the all of the tables related to that query and pulls just the INDEX(s) to the front end, runs against the(se) index(s) and then pulls the related information until the buffer is filled (with a small cushion)... as the user moves thru the records then Access anticipates what the user is doing and may pre-fetch a few records on one side or the other
If the tables are not indexed then the entire table is attempted... this can cause performance issues if the table is large and/or the PC has a small amount of available RAM.
If there is a mix of indexed and non-indexed then the engine attempts to optimize the search method by attempting the search with indexs and moveing on from there.

In VBA it is my understanding that when one uses a snapshot or some of the other methods of opening a record set that all of the related records are pulled, dynamic works as above.

[zmbd]

Mind you these are both Wikipedia articles and as such it should be kept in mind that occationally things are not as on-point as it should be; however, with that in mind, the following are really very good reads and should help you understand how things are working with both the JET and ACE database engines.

Microsoft_Access (a history)

Microsoft_Jet_Database_Engine

[Taaner]

Thanks for the replies Z. Since we have both version 2003 and 2007 applications the history is part of the story.

And indeed it are the engines that handles this so I have to study how these work.

I already read some topics about the need of migrating .mdb's to .accdb's when every user is moved to Office 2007 or 2010. And it seems this can help for performance but in other cases repsonse times are worse? Do you (or someone else) have experience with this?

[zmbd]

I've not seen any issues with either MDB or ACCDB, even for fairly large files. However, the HUGE files are stored on a either a SQL Server or a MYSQL server where I work.