ODBC access over network to *.mdb

问题:

I have a general question of how ODBC with *.mdb works.

In my understanding, and please correct me if I'm wrong, no matter if I use odbc or not, when accessing an MS Access database (*.mdb) that is stored on another computer, i.e. a NAS, my computer first needs to load the full *.mdb file before it can do any operations on it, no matter how simple they are. Much unlike an SQL server who just sends back the result to a query.

If you can confirm this, then I have a few related questions with regards to multiuser environments where the mdb is accessed via odbc:

• How often is the file saved back to the store (i.e. the NAS)? Is there some kind of "idle" timeout or similar? I am trying to figure out the network traffic this generates with larger files.
• How quickly can one user see the changes made by another on the same file?
• How can this kind of file access work in a multiuser environment without constantly leading to corrupted data?

Please note that I do know about better and more efficient solutions, I am really just interested in the inner workings of this specific situation.

Thank you

回答1:

In my understanding, and please correct me if I'm wrong, no matter if I use odbc or not, when accessing an MS Access database (*.mdb) that is stored on another computer, i.e. a NAS, my computer first needs to load the full *.mdb file before it can do any operations on it, no matter how simple they are.

You are wrong. The entire .mdb (or .accdb) file will not be pulled across the network when the database is opened. Even operations on a particular table will not necessarily pull the whole table over the network if indexes can be used to identify the relevant row(s). For real-world measurements performed using Wireshark, see my answer here.

How often is the file saved back to the store (i.e. the NAS)? Is there some kind of "idle" timeout or similar? I am trying to figure out the network traffic this generates with larger files.

The Access Database Engine may buffer updates for a few seconds before committing them. This is probably done to reduce contention on the database file. When the updates are committed, the Access Database Engine only sends the modified pages back over the network; it does not re-write the whole table (or file).

How quickly can one user see the changes made by another on the same file?

I ran some tests a while ago and found that changes made by one user (connection) are normally available to other connections after about five (5) seconds. See my answer here for details.

How can this kind of file access work in a multiuser environment without constantly leading to corrupted data?

Each user has their own instance of the Access Database Engine that manipulates the shared database file, but the various instances work in co-operation with each other to manage record/page locking. Note that when an Access database file (.accdb or .mdb) is opened in "shared mode" a lock file (.laccdb or .ldb) is created in the same folder. The various instances of the Access Database Engine use this lock file to manage multi-user access.

Example re: network traffic

I just used Wireshark to capture the network traffic generated by a VBScript that uses ODBC to run

SELECT COUNT(*) AS n FROM TestData WHERE ID=1

against an 84.3 MB Access database file residing on a network share. The file consists of that one (1) table which contains one million (1,000,000) rows and has the Long Integer [ID] field as its Primary Key.

The total network traffic resulting from

• opening the ODBC connection to the database file,
• running the query,
• returning the results, and
• closing the connection

was 110 KB.

回答2:

You have a good question and I can't answer it!

However:

If you are using and access front end and data with multiple users MS recommend:

1. You split the database into two files. One has the tables the other has everything else (ie the front end file)

2. each users has a copy of the front end file (this is essential sometimes, but you can get away without doing this, but it's NOT advisable as some code can screw multiple users up!)

So it's only data that gets pushed around, not forms and report definitions.

Here some interesting pages here

In short:

If you are working with a Microsoft Jet (.mdb) database, it is more efficient to use the DAO classes than the Microsoft Access ODBC driver.