Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Angry Unanswered: Access 2000 - Database Locked when opened by a specific computer.

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    4
    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    check the copy access locking
    check if access is trying to open in exclusive mode
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2012
    Posts
    4
    Quote Originally Posted by healdem View Post
    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.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2012
    Posts
    4
    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.

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •