Database has been locked by user

[belovedcej]

I have a 2000/2003 format database used only for reporting.

It has linked tables to an SQL Server database.

It automatically opens a form with two buttons on it (no data) and hides the database window and the regular menu, replacing it with my own toolbar.

The default open mode for the database is "Shared."
The default record locking for the database is "No Locks"
On the opening form, the record locking is set to "no locks."

Yesterday, no problem for two of us to share it.

However, when I open it on my computer (not to edit - to use. I simply open it - not in exclusive mode.), and then another person opens it, they get the message that says "the database has been placed in a state by "Admin" on machine (my machine) that is locked and will not be able to be opened." (not verbatim, but close.)

When the other person opens it first and then I try to open it, I get a message that I don't have exclusive access and will not be able to save changes. I have to hit okay about 6 times, but then it opens.

Some users at off-site locations are having problems sharing - some are not.

Any ideas?

 

[Remou]

If this is a front end, and it appears to be, each person should have their own copy.

It seems possible that a query or a form has been changed and not saved. This will lock such a database.

 

[belovedcej]

Unfortunately, we can't give each person their own copy due to network regulations (long story.) And it shouldn't matter because it serves no purpose except reading reports. AND usually there are only 2 people at most opening it, so it shouldn't matter.

But here's the thing - the form is not changed! No one has access to design view.

Person 1: double clicks file to open mdb. It opens to a single form with two buttons - nothing else. They then do absolutely nothing but walk over to person 2.

Person 2: double clicks file to open mdb. Gets an error.

I have researched this some more and have seen an issue when there is Adobe Acrobat installed. That will solve some of the issues, but not all, because two people are having issues even though neither have the full version.

 

[lameid]

I have seen exactly this issue when one user has FILE permissions to read the folder the database is in and the other user has change permissions. This has to do with the creation and maintenance of the LDB file. The Read user puts a lock on the MDB which keeps the change user out when it can't create the LDB file and keeps out users that can creat the LDB file. Conversely if the Change user is in first, the Read user cannot add a record to the ldb file and is kicked out by access.

The interesting thing is if a Read permission opens it another person with read permission can open it. Conversley this is true with Change permission.

Now, the reason this is a real pain... Everytime Access compacts and repairs a database it makes a copy in the same directory and deletes the original. This means the new version inherits the permission from the folder so there is no such thing as just setting permission on the file.

A workaround to this is to create a sub folder that has permissions appropriate to the mdb. Then you compact and repair into that folder forcing it to inherit the permissions of that folder, then when you move the file up one folder (the key is that it is on the same Server), it should retain its permissions.

 

[lameid]

Maybe this isn't exactly your problem as my experience says it would never open if someone else has it opened but if they closed it between your fifth and sixth try it would work. It is just the only thing I have known to force the exclusive access message when the client is set to open shared.

 

[belovedcej]

lameid - interesting - I had never heard of this, but it makes sense. Unfortunatly, that doesn't answer it either.

The database is in a shared folder that all users have full permissions to. I also turned off compact and repair on close because of some problems we were having if someone happened to open the database when another person was in the middle of closing. So instead, the compact and repair is a menu option that is clicked about once a week.

We have narrowed it down to this: at my workplace, the only issue is the Adobe Acrobat. People who do not have full version of Adobe can open it concurrently.

But at another workplace, that isn't so. They still get errors even though no one has it opened.

I have decompiled and recompiled and compacted and repaired the database and resent it to the user. Maybe by some miracle that will help. I never knew working with computers would make me so superstitious!

 

[lameid]

I have been using Front Ends on clients since before Access 2000 came out so my shared front end experience really is not usefull. Looking back at Remou's post, perhaps all the queries are not complied and that is causing the lock problem?

Locking may just be evil in shared front ends... If it HAS to be on the network, then maybe each user could keep it in a home directory. Then that would alleviate any locking problems. You could even use the same code you would use to update frontends (example in one of the FAQ's) to push them out. The catch 22 is someone will complain about the backup size. If it makes sense that would be an excellent time to say... if only there were someplace off the network each client could get to that is not backed up. You might find that you are suddenly allowed to put front ends on the client side Diabolical isn't it?