MS Access Data Access Limitations

Question

I have a project right now where I'd like to be able to pull rows out of an Access database that a 3rd party product uses to store its information. There will likely be a small number of users hitting this database at the same time my "export" process does, so I'm a little concerned about data integrity and concurrent access.

Will I likely run into problems with my .NET import process (using LINQ/ADO.NET/?) when it is trying to pull data out of the MDB at the same time someone else is saving a row? How does Access's locking work?

Answer 1

There should no problem. Problems can occur only on concurrent write operations. The locking from MS Access based on file locks in the ldb file. The locks occur only on pages and not on the completely file. Because the locks are in the ldb file and not in the mdb file that there are no problems with parallel reading.

Answer 2

When you open the database, do not attempt to open in read-only mode (although you might think it makes sense). When you are the first user in, Access opens the mdb file in read-only mode and does not create an ldb, forcing all subsequent users to be in read-only mode as well.

Answer 3

In previous workings with Access (back when I was using 2003 for things) the only thing I ran into was that occasionally a read would lock rows just above and below the current read. However, I believe this may have been an isolated issue with our application.