Results 1 to 9 of 9

Access error Could Not Save; Currently Locked By Another User

  1. #1
    Bigdoggit is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    27

    Access error Could Not Save; Currently Locked By Another User

    I have been struggling to deal with this and have done a lot of internet searching and forum searching trying to figure it out but I end up with more questions than answers. My users (anywhere from 8 to 25) intermittently get the error message "Could Not Save; Current Locked By Another User". The only solution seems to be everyone exiting the database then getting back in. I searched Microsoft information and forum information to understand the locking process. I gather that Access 2000 was the start of Record Locking and that record locking is actually Access opening a record and filling up a full page of memory then locking that page which means there is no real record locking in Access. I have looked at the Tool\Options\Advanced menu and understand the Default Record locking settings these are global settings. There is some suggestion that these are then only applied to NEW forms but existing forms retain their existing locking mechanism. Not sure if that is true. Also, the "Use Record level locking" checkbox being checked indicates record locking and unchecking indicates page locking. Now the problems.

    No matter how much testing I do I cannot reliably recreate the "Could Not Save; Current Locked By Another User". I have tried several types of settings, used different computers, even different logins, every time Access uses optimistic locking. The only exception is when I set the form to use record locking. However, the behavior of this is far different from the problem my users experience. They are in the middle of editing a record and when they try to advance (thus save) the record the application hangs then produces the "Could Not Save; Current Locked By Another User" error.

    I hope someone has not only a suggested solution but a method to create this error reliably. Without that any solution looks like a guess. Thank you in advance for any help.

  2. #2
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,907
    You can minimize the locking issue if your forms are based on queries rather than directly to tables.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Bigdoggit is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    27

    Yup

    They are. Have you experienced a way of recreating this error message reliably?

  4. #4
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,907
    What is your system design? MultiUser with Split FrontEnd and BackEnd and everyone has their own copy of the FrontEnd on thir local PC?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    Bigdoggit is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    27

    design is.......

    This was designed by another individual. This is one database, no front end and back end. Does that make a difference in how to produce this error? I surely want to get rid of it but I want to be able to produce it to test the fix. I can't even produce the error. I also can't produce record locking using the advanced options provided in the database.

  6. #6
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,907
    Are multiple users in the same db at the same time? Or is the error caused when only one use in in the db?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    Bigdoggit is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    27

    Multiple

    I have only seen the error when multiple users are in the database.

  8. #8
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    Correction: If a user cannot open the mdb/accdb and get's the 'locked by another user type error'....(but see bottom part of this post!!)

    You'll get this error when multiple users are in the mdb/accdb file and one of them 'sits' on a record or the form (or table structure) is not designed optimally or someone opens the mdb/accdb who doesn't have both read and write permissions to the folder and file. This error has to do with the *.ldb file associated with the *.mdb/accdb file which is actually what is 'locking' and causing the error. You have a couple of options (but should split the front-end/back-end as Allan suggested regardless).

    1. Put the Front-End on everyone's desktop so each one is in their own Front-end.
    2. Have everyone open the mdb/accdb using a script such as in this example: http://www.accessforums.net/code-rep...sier-7572.html

    An *.ldb file typically tends to 'lock' when there are 3 or more users in the same mdb/accdb (the max recommendation for the same MSAccess file), especially when it constantly has a table open and their is no activity (or again, poor form design or poor table structure). The *.ldb get's confused on whose in/out of the mdb/accdb as it keeps track of this information. If a user for example, doesn't close out of the mdb/accdb properly, the *.ldb file doesn't know what's happened to that user and hence, locks everyone from opening the mdb/accdb (for example, Citrix users may terminate their citrix session instead of logging out properly while in an mdb/accdb and cause a locked *.ldb file). Of course, good form design also plays a role. A poor form design "can" affect an *.ldb file when that form design is constantly tapping into the table incorrectly (again, along with table structure). Ideally, you want to keep users from always having a 'table' open constantly. I prefer to have a MainMenu type of form that doesn't have any table as a recordsource. Users then close to the MainMenu which prevents the constant connection to keeping a table open which in a sense, times-out so to speak after inactivity and leads to confusion with the *.ldb file. Of course, the network itself plays a role to some extent in this scenario (an unstable network connection for a user can cause havoc on an mdb/accdb.)

    Once you do get this 'lock' error (when users try to open the mdb/accdb), your solution is to find 'everyone' whose in the mdb/accdb, have them all close out. Make sure there is no *.ldb file showing in the folder (if there is, open and close the mdb/accdb to see if it goes away - if it doesn't, someone is either still in it or if you're absolutely positive everyone has closed out, have your network guys delete the *.ldb file). Then open/close the *.mdb/accdb (and to be safe, run a compact/repair on the mdb/accdb.) Once everyone is out of the mdb/accdb (and there is no *.ldb file showing), then you can have users start getting into it again. If the error happens again, try to pinpoint who is causing the error. It could be something with that specific user's connection that is flaky and causing the problem (or again, revisit your design.)

    ----------------------------------------------- (more likely your cause of the error you mentioned......)----------------
    If a user CAN open the mdb/accdb but gets 'CANNOT SAVE RECORD....'locked by another user error' on a form (the above is also a possible cause but most likely it's due to form/table design.) This typically appears when a user tries to go to another record and is usually accompanied by the 'Copy to Clipboard' message.

    You need to first look at your form design. Try possibly adding some refresh commands on your form in certain places (I can't tell you where unless I see your design.)

    See this example: http://www.accessforums.net/code-rep...tion-7761.html to show how to reproduce locking a record with form design/code. Basically, If you have 2 forms open based upon the same table (and both forms are updating or code is also updating that same table), typically you'll also get the message to the effect 'Copy to clipboard....etc... (if you ever get the 'Copy to Clipboard in the message, this almost always means that you need a refresh command (or requery command) somewhere in the code to tell MSAccess to refresh an existing form open based upon that same table.) For example (in the example in the link), if you have a form open and update field XYZ but then open another form (based on the same table) and update field XYZ on that form, you'll most likely get the 'copy to clipboard error' after closing the 2nd form and then try to go to another record on the 1st form unless you somehow refresh the 1st form open that has field XYZ on it before going to another record (the same is true if you're updating field XYZ in code.) This is where the 'intermitten' part can drive you crazy and you need to look for what code (or other form) is updating that same field in the same table.

    Fix example (updating XYZ in code):
    me.XYZ.value = "SomeValue"
    Refresh

    If this doesn't work and it's accompanied by the 'lock by another user' error, you'll need to most likely look at your form design, specifically the recordsource of your form. (as a general rule I go by, 1 table (being updated) as a recordsource per form - ie. don't try to link multiple tables and update those multiple tables on 1 single form - use subforms or popup forms (or code) to update the other relational tables.) This again, is where the 'intermitten' part can drive you crazy.

    If you're still getting the error, then have someone review your relational table structure to see if this is a possible cause. I've seen some relational structures cause problems regardless of how well the forms are designed.

    Last Notes: The 'locked by another user' is a horribly vague message by MSAccess since it can be due to a couple of different reasons. It's not to be confused by a specific reason (such as always meaning a table lock). It can be that or it can be the *.ldb file itself or it can be form/table design or even permissions related. It depends on the scenario in which you get the error. (DO NOT start changing MSAccess default settings to try and avoid this error - this will only cause more problems and make it more difficult to troubleshoot/fix.)

  9. #9
    Bigdoggit is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    27

    So I should be able to reproduce the error

    I want to reproduce this error but am having trouble with that too. I thought it had to do with the Tools\Options\Advanced settings but it sounds like it doesn't according to your post. It sounds like it CAN happen when 4 or more people are logged in. And then it is just a crap shoot of when it actually will happen. Does that sum it up accurately?

    The form is getting data from a query, not from a table directly. Should that be helping at all? Otherwise it sounds like the only solution is splitting the front end from the back end. However, if everyone is using the same form that is based on a query why would a separated front end make any difference? Is there an easy way to explain that to me too? It would be nice to understand the nature of the problem a bit better.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Locked
    By anoobus in forum Database Design
    Replies: 3
    Last Post: 03-01-2012, 02:02 AM
  2. Run-time error '3734': database is locked ...
    By cowboy in forum Programming
    Replies: 6
    Last Post: 07-26-2010, 07:01 AM
  3. Locked out of my Access POS
    By oldhiway in forum Access
    Replies: 5
    Last Post: 05-23-2010, 09:04 PM
  4. Replies: 3
    Last Post: 03-30-2010, 07:48 AM
  5. Replies: 0
    Last Post: 11-10-2008, 10:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums