none
Locked Access db - can't compact or delete ldb

    Question

  • I have VB 2005 windows application that is reading and writing records into Access 2003 database. After I deployed application to multiple servers - the database stays locked all the time - even if nobody has it open. When I check open files through Computer Manage - it doesn't show that anybody has it open. Yet - I can't delete .ldb file, I can't repair and compact the database to replace existing database, I can't rename database to replace it with other version. Please help! Thanks, Alla

    Thursday, August 23, 2007 12:55 PM

Answers

  • Alla2552,

     

    According to your problem on deploying Access database issue, I would like to provide you the following KB articles and hope that can help you to understand the problembetter.

     

    1. Introduction to .ldb files

     

    For every database that is opened for shared use, an .ldb file is created to store computer and security names and to place extended byte range locks. The .ldb file always has the same name as the opened database (.mdb), and it is located in the same folder as the opened database.

     

    Whenever the last user closes a shared database, the .ldb 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.

     

    If you plan to share a database, the .mdb file should be located in a folder where users have read, write, create, and delete privileges. Even if you want users to have different file privileges (for example, some read-only and some read-write), all users sharing a database must have read, write, and create permissions to the folder.

     

    For each person who opens a shared database, the Jet database engine writes an entry in the .ldb file of the database.

    2. You receive a "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later." error message when you try to open database objects in Access

    If you open the database, close the database without closing Access, and then you open the database again exclusively, you may be able to view the database object in Design view. When you open the database the first time, the .mdw file is accessed, and therefore the .ldb file is created. Two active-connection entries are written to the .ldb file. One active-connection entry is for the .mdw file, and the other active-connection entry is for the .mdb file. When you close the database, the second active-connection entry in the .ldb file may be removed or may be modified to indicate that the user is no longer connected. Subsequently, when you open the database exclusively, Access may not create an .ldb file or may not write an entry to the .ldb file because Access detects only one active entry in the .ldb file. You can open a report in Design view because Access detects only one user in the database.

     

    Hope that can help you.


     

    Tuesday, August 28, 2007 7:52 AM
  • From my experience implementing Access-based solutions--and I love 'em!!--it's unrealistic to expect a server-located MDB shared by 30 users to ever be completely unlocked and "free" for maintenance activities such as compacting, no matter how well your code is written from a connection.close perspective.  E.g., all you need is a single user needing to force-quit their app and you're locked.

    My recommendation would be a maintenance window during which you bounce the server that's hosting it to free it up and then compact it.  Depending on the situation, you might need to put it on a separate file-sharing server; to be truly safe with this, you'd probably want to bounce the box and unplug its network cord, ensuing that nobody locks the database, and when the compact/rename is done, plug it back in.

    Or, you could have a scheduled process copy the MDB file into a separate file, then compact it (by referencing the DAO COM control in your VB app, you can programmatically compress an Access database, but it's still subject to the exclusive-lock aspect of things), but any changes made in between the copy and when you "swap" out the compacted version with the "production" version would be lost.
    Monday, September 10, 2007 1:48 PM

All replies

  • Alla2552,

     

    According to your problem on deploying Access database issue, I would like to provide you the following KB articles and hope that can help you to understand the problembetter.

     

    1. Introduction to .ldb files

     

    For every database that is opened for shared use, an .ldb file is created to store computer and security names and to place extended byte range locks. The .ldb file always has the same name as the opened database (.mdb), and it is located in the same folder as the opened database.

     

    Whenever the last user closes a shared database, the .ldb 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.

     

    If you plan to share a database, the .mdb file should be located in a folder where users have read, write, create, and delete privileges. Even if you want users to have different file privileges (for example, some read-only and some read-write), all users sharing a database must have read, write, and create permissions to the folder.

     

    For each person who opens a shared database, the Jet database engine writes an entry in the .ldb file of the database.

    2. You receive a "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later." error message when you try to open database objects in Access

    If you open the database, close the database without closing Access, and then you open the database again exclusively, you may be able to view the database object in Design view. When you open the database the first time, the .mdw file is accessed, and therefore the .ldb file is created. Two active-connection entries are written to the .ldb file. One active-connection entry is for the .mdw file, and the other active-connection entry is for the .mdb file. When you close the database, the second active-connection entry in the .ldb file may be removed or may be modified to indicate that the user is no longer connected. Subsequently, when you open the database exclusively, Access may not create an .ldb file or may not write an entry to the .ldb file because Access detects only one active entry in the .ldb file. You can open a report in Design view because Access detects only one user in the database.

     

    Hope that can help you.


     

    Tuesday, August 28, 2007 7:52 AM
  • Thanks. The theory sounds good - but I'm not sure what to do in existing situation. I have 30 users and not sure which one locked it. It looks like one of the servers has it locked - when we rebooted all servers as a maintenance - it released it temporary, but it got locked again by somebody. Or may be I'm missing something in the code and it keeps it locked? I do close all connections to database from application. Is there any other commands that I need to use - other then close?

    Monday, September 10, 2007 12:49 PM
  • From my experience implementing Access-based solutions--and I love 'em!!--it's unrealistic to expect a server-located MDB shared by 30 users to ever be completely unlocked and "free" for maintenance activities such as compacting, no matter how well your code is written from a connection.close perspective.  E.g., all you need is a single user needing to force-quit their app and you're locked.

    My recommendation would be a maintenance window during which you bounce the server that's hosting it to free it up and then compact it.  Depending on the situation, you might need to put it on a separate file-sharing server; to be truly safe with this, you'd probably want to bounce the box and unplug its network cord, ensuing that nobody locks the database, and when the compact/rename is done, plug it back in.

    Or, you could have a scheduled process copy the MDB file into a separate file, then compact it (by referencing the DAO COM control in your VB app, you can programmatically compress an Access database, but it's still subject to the exclusive-lock aspect of things), but any changes made in between the copy and when you "swap" out the compacted version with the "production" version would be lost.
    Monday, September 10, 2007 1:48 PM
  • Thanks for the suggestion. The unplugging wouldn't work - we have multiple servers that work 24-7 for multiple applications. Once in a while we reboot all servers as a maintenance - that's my only window of opportunity. The separate file idea won't work either - since I can't replace/rename/delete locked database. But I do appreciate your answer.

    Monday, September 10, 2007 2:51 PM
  •  

    Great Information. 

     

     Is there a way around this? "all users sharing a database must have read, write, and create permissions to the folder. " I'm trying to set up a shared folder on 2003 server , but i want to prevent Users from adding files, deleting files on the shared folder where my .mdb is.  Currently domain usergroup has those permission and i find multiple copies of my .mdb's on the same shared folder.   I'm just glad that no ONE has accidently deleted it.  

     

    any helpful tips would be greatly appreciated.

     

    Thanks

     

    Wednesday, October 10, 2007 11:59 PM
  • Hi Jason / All,

    I got a client application which is built using .net windows application and this uses Microsoft Access as its backend database server.

    We have a production environment where each and every system consists of its own Ms Access db file to communicate with the front end .net application.  Once all the work is completed the Ms Access db file is transferred to the server.

    Recently a month ago the system has developed an issue ie., Whenever the end user works and closes the .net application the Ms Access db file is editted and whereby causing a .ldb lock file.  But this lock file is not getting removed even after closing the connection. 

    There are 2 options to Exit from the .net application, ie., 1. Click "Exit" and return to Login window.  2. Click close and kill the exe completely.  To my surprise, the lock file is not getting removed when i click using "Exit" whereas the lock file removes when i Close the Exe completely.  Please reply me as its killing the production time.  Kindly reply ASAP.  Thanks a lot in advance...... Regards..... Daniel

    Monday, March 26, 2012 12:15 PM
  • I have experienced this problem and there is a bug somewhere... if you search the net it's all the same comments again and again, somebody is still logged in, ms access is running in the background and so on and so fort.. Nobody believes the fact that for no reason the ldb file cannot be deleted.

    ...but I found an unusual and simple solution to this issue... and posted it here:

    (Spam Site Removed) in the blog sektion..


    Thursday, August 30, 2012 12:22 PM
  • Please provide an appropriate link or your post will be marked as abuse for advertisement.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, August 30, 2012 1:30 PM