3

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." access 97 "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.

2

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.

| improve this answer | |
  • I checked and the database is set to 'shared access mode'. The way the client program works is it looks to see that there is no .ldb file, indicating nobody has the DB open. Yeah, it's bad, worse than you are probably imagining. Unfortunately we're stuck with this poorly designed solution for at least the time being. It's quite integrated into the rest of our systems and the original developer didn't realize the problems until he left and we started scaling it up. – Daniel Andrews Mar 23 '18 at 18:21
  • There are always ways out, and it's usually pretty easy to get people up and away from Microsoft Access. Normally, it's basic CRUD and simple forms. And you can easily move the database into SQL. – Evan Carroll Mar 23 '18 at 19:35
  • Unless people hard-coded stuff everywhere...then it’s a nightmare. :( – clifton_h Mar 29 '18 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.