locked
Access Database Clear Lock Files RRS feed

  • Question

  • I have an access database, written in 2007 that refreshes data from other sources on a schedule.

    I have it scripted to update the macro that refreshed the database and then compact the database as I will pst below.

    The problem is people leave themselves logged in to the Database and it causes the script to fail. How can I clear any user locks on the database via the script, is it even possible?

    This is what I have below.

    "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\db\Genesis\Genesis.accdb" /x Update /excl
    "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\db\Genesis\Genesis.accdb" /compact

    Tuesday, December 4, 2012 4:13 PM

Answers

  • Hi,

    Based on my research, the locks are stored in a locking information file. The file format is *.laccdb in Access 2007.

    For every database that is opened for shared use, a locking information file created to store computer and security names and to place extended byte range locks. The locking information file always has the same name as the opened database (.accdb), and it is located in the same folder as the opened database. For example, if you open (for shared use) the Northwind.accdb sample database in the C:\Program Files\Microsoft Office\Office 12\Samples folder, a file named Northwind.laccdb is automatically created in the same folder.

    Whenever the last user closes a shared database, the .laccdb file is deleted. The only exceptions are when a user does not have delete rights or when the database is marked as corrupted. Then, the .ldb file is not deleted because it contains information about who was using the database at the time the database was marked as corrupted.

    So in a word, you can delete the .laccdb file to clear the user locks.

    For more information, please refer to the following links:

    http://support.microsoft.com/kb/289681

    http://support.microsoft.com/kb/299373/en-us


    Jaynet Zhang
    TechNet Community Support

    • Marked as answer by Jaynet Zhang Tuesday, December 11, 2012 2:16 AM
    Wednesday, December 5, 2012 2:44 PM
  • You cannot delete the lock file if someone is still in the db unfortunaly. and Jaynet is 100% correct on her reaserch.

    There might be more than 2 ways of dealing with this issue. But here are 2....

    1. You can create a hidden form that runs when the db is opened (autoexec macro setting the form to oipen hidden) then on the timer event open the code builder and put in docmd.quit then in timer interval you can put in milliseconds how long till it closes so that no one will have it open longer than say 12 hours. who would need it open longer than that.  eg 43200000 would be 12 hours.  if they work an 8 hour day do it to 28800000. be aware i do not know what the impact would be in preformance.  If the db does not do that much in processing a ton of stuff then it should be ok.  I would just test it for a little while before rolloing out to a bunch of users.

    2.  Write a VBScript that copies the database from a network location to the persons computer then opens it.  This would give you a few advantages.  Never having to worry about a user having an old version of the db.  YOu can make changes all you want to the version they copy from and are never locked out from design changes. If some one leaves it open who cares. and you have 0 worries about to many users in the same db and corrupting data.Only issue if your db is not split or on a Server db application(MS SQL,DB2, or others) you must do one of them otherwise everyone will be using diffrent data sets. Then send the users the script instead of the shortcut to the db.

    The choice is yours there are many examples of the script if you are not used to writing that.


    • Edited by JohnBlanch Saturday, December 8, 2012 4:02 PM Forgot something
    • Marked as answer by Jaynet Zhang Tuesday, December 11, 2012 2:16 AM
    Saturday, December 8, 2012 4:00 PM

All replies

  • Hi,

    Based on my research, the locks are stored in a locking information file. The file format is *.laccdb in Access 2007.

    For every database that is opened for shared use, a locking information file created to store computer and security names and to place extended byte range locks. The locking information file always has the same name as the opened database (.accdb), and it is located in the same folder as the opened database. For example, if you open (for shared use) the Northwind.accdb sample database in the C:\Program Files\Microsoft Office\Office 12\Samples folder, a file named Northwind.laccdb is automatically created in the same folder.

    Whenever the last user closes a shared database, the .laccdb file is deleted. The only exceptions are when a user does not have delete rights or when the database is marked as corrupted. Then, the .ldb file is not deleted because it contains information about who was using the database at the time the database was marked as corrupted.

    So in a word, you can delete the .laccdb file to clear the user locks.

    For more information, please refer to the following links:

    http://support.microsoft.com/kb/289681

    http://support.microsoft.com/kb/299373/en-us


    Jaynet Zhang
    TechNet Community Support

    • Marked as answer by Jaynet Zhang Tuesday, December 11, 2012 2:16 AM
    Wednesday, December 5, 2012 2:44 PM
  • You cannot delete the lock file if someone is still in the db unfortunaly. and Jaynet is 100% correct on her reaserch.

    There might be more than 2 ways of dealing with this issue. But here are 2....

    1. You can create a hidden form that runs when the db is opened (autoexec macro setting the form to oipen hidden) then on the timer event open the code builder and put in docmd.quit then in timer interval you can put in milliseconds how long till it closes so that no one will have it open longer than say 12 hours. who would need it open longer than that.  eg 43200000 would be 12 hours.  if they work an 8 hour day do it to 28800000. be aware i do not know what the impact would be in preformance.  If the db does not do that much in processing a ton of stuff then it should be ok.  I would just test it for a little while before rolloing out to a bunch of users.

    2.  Write a VBScript that copies the database from a network location to the persons computer then opens it.  This would give you a few advantages.  Never having to worry about a user having an old version of the db.  YOu can make changes all you want to the version they copy from and are never locked out from design changes. If some one leaves it open who cares. and you have 0 worries about to many users in the same db and corrupting data.Only issue if your db is not split or on a Server db application(MS SQL,DB2, or others) you must do one of them otherwise everyone will be using diffrent data sets. Then send the users the script instead of the shortcut to the db.

    The choice is yours there are many examples of the script if you are not used to writing that.


    • Edited by JohnBlanch Saturday, December 8, 2012 4:02 PM Forgot something
    • Marked as answer by Jaynet Zhang Tuesday, December 11, 2012 2:16 AM
    Saturday, December 8, 2012 4:00 PM