Access "couldn't use ... file already in use"

Question

I have an Access 97 database that is used by some software on multiple (up to 4-6) computers (running Windows 7). Once in a while (maybe 1-2 months) something happens where computers on the network can't access the database.

The program accesses the database through a ADO/JET driver (the program is written in LavVIEW) and reports "Could not use ... file already in use." When this happens attempting to open the database on another computer in Access gives a similar message:

"Couldn't use '.mdb'; file already in use."

When this happens there isn't an associated .ldb file with the .mdb. The last time this happened I restarted the four computers that were running the software and I was immediately able to open the DB with Access on another computer. It seems that caused the problem to go away, but it is possible it was a coincidence.

From what I have read this problem can happen on a network if computers are unable to create/modify the .ldb file. The .mdb file is on a Novell network drive that all the computers have read/write/create permissions on, and everything works fine until the computers stop being able to open the file.

Does anyone have any ideas of what could be causing this? I think one possibility is the computers actually cannot write the .ldb file (the software normally doesn't have a connection open to the database, so most of the time there isn't a .ldb file). I would have to check that by trying to write a file to that directory next time this problem happens. It seems like a strong possibility is something else hanging it up though, especially since restarting the computers seems to fix it but I'm not very familiar with Access, ADO, and JET.

Answer 1

From what I have read this problem can happen on a network if computers are unable to create/modify the .ldb file. The .mdb file is on a Novell network drive that all the computers have read/write/create permissions on, and everything works fine until the computers stop being able to open the file.

What you're doing is very dangerous. Database engines handle MVCC. File I/O is not sufficient. That said, Microsoft Access has a method to get around this called Shared Access Mode. I wouldn't suggest using this but it should get around the error problem.

Make sure that Access is set to open in shared mode on all of the users' computers. This is the default setting, but you should check to be sure — if a user opens the database in exclusive mode, it will interfere with data availability. Complete the following procedure on each computer:

1. Start Access and under File, click Options. Note, if you're using Access 2007, click the Microsoft Office Button and then click Access Options.
2. In the Access Options box, click Client Settings. Note, if you're using Access 2007, click Advanced.
3. In the Advanced section, under Default open mode, select Shared, click OK, and exit Access.

As a side note, it's my bread and butter migrating people away from Microsoft Access, Visual Basic, and Perl. Any decent consultant salivates at the mouth when they read about this for the implications to provide value. Shared Access Mode should not be used without extensive backup solutions. Things will go wrong, and when they do expect downtown. I have a few clients on this and we sync their stuff up to AWS every 15 minutes.

Note, I see you're on Access 97. I'm not sure when they introduced Shared Access mode, but you'll need to upgrade if that option isn't there. I would really not want to work around that, it's not in your best interests if you're advising them, or theirs.