3

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

vic
  • 292
  • 3
  • 14

2 Answers2

7

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.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    Ah great someone who knows what's what! I've never seen anything about how this actually works. I'm very interested. Heres a few Q's: A NAS can do no processing, so when Access on my PC gets data from TABLE1 in an accdb file on a NAS, it must grab the whole index of the table to work out which rows it needs (and hence with pages of data), then it gets a subset of the data in the table. Right? If there are no indexes I suppose it will grab enough data from the table to satisfy the users needs (eg just display 100 rows, or get the whole table to summerise fields 1,2 & 3). – HarveyFrench Aug 13 '15 at 21:49
  • 2
    Access will read only enough of the index to identify which records must be retrieved. Consider a query which asks for one autonumber value which happens to be the first entry of the index. It really only needs to examine that first entry to find the pointer to the matching physical record. In reality, indexes like tables are stored and accessed via pages of data ... so Access would need to read only the first index page in that hypothetical example, not the entire index. If an index does not exist, or will not be used, most likely Access must read the entire table (full table scan). – HansUp Aug 13 '15 at 23:54
  • 1
    One thing to be carful of. Not all NAS devices support the type of file sharing that Access uses. If it doesn't, you will experience excessive BE corruption in a multi-user environment. I found this out years ago when it happened to a client. Sorry, I don't remember the technical details. – AVG Aug 14 '15 at 00:52
  • Thanks for your answer. I still have more questions than answers but you sent me on the right path. I found [this explanation](https://github.com/brianb/mdbtools/blob/master/HACKING) and understand now that what you describe is possible because the mdb has a fixed structure using pages. But this type of access can only work if the program in question can directly communicate with the file system. So I assume that SMB offers some kind of functionality to do so on a remote system. Would you happen to have some more detailed info on that? I couldn't find anything helpful yet. – vic Aug 15 '15 at 00:51
  • I did some more research and I think I found what I was looking for. SMB indeed allows for file open calls to specify a byte range, and the same goes for write calls. So I simply assume that Odbc must have those implemented accordingly. The rest then boils down to what Gord and Hansup described, to optimize queries and create a smart index. Can anyone confirm? – vic Aug 15 '15 at 01:31
  • Thanks Gord, appreciate it. – vic Aug 16 '15 at 09:36
0

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.

HarveyFrench
  • 4,440
  • 4
  • 20
  • 36