MS Access split database queries

Question

When the frontend of a MS Access db queries a MS Access backend on another machine over the network, does it pull in the whole table/database/file, or does it have some way of extracting just what it needs from the backend, thereby lessening network load.

Answer 1

The Access Database engine will only will read what it needs. However, this might mean more data is transferred across the network than you might think.

For example, if the table is indexed so that a full data scan is not needed to find your record, then it will read the index first, then only those records to satisfy the query results. Minimal data is moved across the network.

However, if you only return one row from a large table where there is no index, then the full table will be transferred across the network. This is because the query process is running on the client machine, and it needs to read all the data to do a table scan.

Thus, if you are creating an access app using this method, make sure your tables are appropriately indexed.

Answer 2

It's hard to answer exactly. Do you mean opening the database over a network?

I'm assuming you mean the frontend and backend are in a single MDB file.

When a query is performed (depending on the query), it will only grab the data it needs. This is true for most databases. However when the file is opened, the database integrity is checked.

For more information on the JET database backend, go here: http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine