Hi again, everyone. I'm a long time lurker, first time poster. I have found the solutions to many of my access dilemmas here, and I'm hoping you all can help me out again. I'm having some Access woes with which I have been unable to find help about around the web, as my circumstances don't seem to fit other reports of this problem. I get the dreaded:

The database has been placd in a state by user <name> on machine <name> that prevents it from being opened or locked.


Here's the kicker, its used actively by 2 users, when user 1 opens it, user 2 gets that error (or any other person that tries to get into the database), if user 2 opens it first (or any other user) then any and all users that open it up can use it without any problems.

Now, I don't have any plugins in use, no VB code getting data from other sources, or mail merges, or anything like that, everything in this database is internal. What I can't figure out is ONLY user 1's computer will lock the database and ONLY when she's the first to open it.

Any Ideas?
Avatar

check the default locking method used by each instance of Access
if its A2000 or later use row locking or possibly optimistic locking
do not use open exclusive or table locks

check that none of the users are making design changes, that means not opening forms or reports in design mode, or use code which modifies the design at run time

this is a symptom of a multi user db, you woudl be wise to consider splitting the database into separate parts, one holds the data (the back end) one holds the user interface (forms & reports)

having split the db, then consider deploying the front end as a sepcific copy for each user / computer, ie deploy the front end onto the client computers. there is code in this forum to help you with that

having split the db consider deploying the fornt end as a MDE to stop malicious meddling with the db by users. but ifyou do go down the MDE route make certain you keep an unencrypted db (*.mdb) as this si the only way you can make changes to the database. and keep good backups

I know they aren't in Design view, I have locked this ability out, and neither of them knows to hold the shift key to unlock it. I am leaning more towards creating a frontend for it to avoid this.

check the copy access locking
check if access is trying to open in exclusive mode

I'm not sure about Copy Access Locking, please clarify. And the database is set to open in Share mode, not exclusive mode.

sorry should read check the locking settings
make certain both copies of Access are opening in shared mode, its a setting in Access not the MDB

I just got done creating a front end, so it doesn't really apply anymore (i'm using a duplication trick that I found here with a batch file instead of a VBS), but where is that setting? The only time I can find settings for access is if I have a database open. It would be handy to know for future reference.

I used to get that error message when I would by mistake open the same file a second time on the same machine. Of course, nobody else could open it either without the error. Is it possible that user 1 is simply impatient, opening it twice all the time and, when she gets the error message, she simply ignores it (leaves the error alone) and goes back to the first iteration of the db?

It could be it wasn't in Access but in Excel, or it's a particular version, because I can't duplicate the error using A2003.

Sam