How does an MS-Access SQL query run on a remotely located .mdb file?

Question

I'm trying to understand how making a query to a .mdb file works. Suppose the file is located on a share drive, PC2, I open it programmatically from PC1.

When I make a connection to a .mdb file, I assume no "instance" of MS Access is started on the PC2 (since it's a simple file server). Is this correct?

When I make a SQL query, does it have to copy the table locally, and run the query then return my results and toss away the table and any excess data?

What happens if I "order by" on a query? is the entire query returned, then locally ordered, or somehow ordered remotely?

I'm sure I have other questions, but I'm trying to understand how connecting to an MDB file works from a remote location. (we have a decent amount of latency where I am located, so a particular query can take 9 seconds, which in my case is unacceptable, I'm trying to understand how this is working and if it can be improved).

I'm running with c# in this case, I don't expect that should make much difference, but may in your response.

Answer 1

To add to Gord's answer...

Access databases are accessed through Windows file page locks. My understanding was that Microsoft added this page locking specifically for use by MS Access (but is also available for any file through the Windows API).

Because the instance is local and collisions and conflicts are handled through file page locks, client-server contention is an issue. Access has known issues here. It's why one should switch to SQL Server Express (also free) whenever possible. But, yes, MS Access has a certain level of convenience; SSE has a bigger footprint and a far less friendly GUI

All desktop databases have client/server issues. Gord's answer matches my knowledge. The point of indices is to reduce the amount of table data that needs to be pulled locally. Pulling the index is a relatively small task in comparison to the table data. This is standard index optimisation, although I would say it is even more important for desktop databases due to the remote data and, ugh, file paging.

In general the Access (JET) engine does NOTHING remotely. It's all file data grabs and executed locally in in the local MSA/Jet engine. You know this because the engine is installed locally and doesn't have to be installed on the file host. It is, however, a convenient quick and dirty way of dispersing processing loads. :)

Answer 2

When I make a connection to a .mdb file, I assume no "instance" of MS Access is started on the [remote machine] (since it's a simple file server). Is this correct?

Yes. The application will be interacting with a copy of the Access Database Engine on the local machine, which in turn retrieves the information from the database file on the remote machine.

When I make a SQL query, does it have to copy the table locally, and run the query then return my results and toss away the table and any excess data?

Not necessarily. Depending on the indexing scheme of the tables(s) involved, the Access Database Engine may only need to retrieve the relevant indexes and then determine the specific pages in the data file that contain the records to be retrieved. In some cases it may need to retrieve the entire table (e.g., when a full table scan is required), but that it not always the case.

What happens if I "order by" on a query? is the entire query returned, then locally ordered, or somehow ordered remotely?

The Access documentation says that indexes will speed up sort operations (ref: here), suggesting that the Access Database Engine can retrieve the required rows from the remote file in sorted order.

Answer 3

Your instincts are correct, mdb/mde dbs are just glorified text files which must be processed locally. Here are some tips on network performance: http://www.granite.ab.ca/access/performancefaq.htm

But since SQL Server Express is free, there is almost no excuse for not migrating, especially since Access has a tool to manage that for you. In a low volume multi-user environment (2-10 for example), MS Access can work ok, but for enterprise solutions where a higher volume of users and/or transactions is in any way possible, you are dicing with disaster.