Access Security - How to reset / clear / delete an .LDB file?

I've just added Access Security to an existing database and have signed-on and off as various users during the day.  

However, the LDB file still thinks I have the database open for exclusive use even though I'm not currently using it.  Consequently, I can't delete or replace the database, or open it for exclusive use.

How can I clear or delete the LDB file?
chazzinpaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JimMorganConnect With a Mentor Commented:
chazzinpa:

There is a good explanation as to why logging out and back in again works.  But there is a lot more to it than that.  You really need to understand the process that is used to lock records in a DB.  

Also I don't think that the problems you are having compacting the database located on a server has anything to do with the problem presented by this question.  I feel that the discussion about compacting should be in another question.

After you read the following information you need to decide if any of us have provided you with enough information to solve your problem now and in the future.  Please by no means delete the question.  There is just too much information here that should be preserved and not lost.  Once a question is deleted, it is gone for good.

We have a pact among the experts in Access not to post an answer to a question until it is clear that the expert who marks his comment as an 'answer' knows that he has given the ABSOLUTELY, POSITIVELY BEST answer to the question or unless the asker requests that we 'answer' the question.  We also depend on the member who asked the question to be fair to the experts and not delete the question until there is a concensous that we were unable to answer the question.

Jim

The fact that you could release the files indicates that Novell Netware recognizes that you have logged out and releases all the locks that IT has placed on the files that you were using.  Besides the locks that Access is setting, Netware is setting locks as well.

It makes sense that once you logged out of Netware, you could log back on and delete the files.  After all, you were the last one using them and until you got off, Novell didn't release the locks that Access put on the files.

In order to understand the process better, the LDB is a shared Locking DataBase, which is used to keep track of record locks by each user.  Access automatically creates an LDB file of the same name as the database whenever it finds that one does not exist.  If you experience a GPF or power failure or you reboot while a record is locked, the LDB file will be left in a state that reports which records were locked at the time of the problem.  Because the lock would now have no owner, the effect is a permanent lock of the record, which of course prevents you doing anything with the database.

The solution is to close the database (in a multiuser environment, all users must close it) and delete the LDB file.

In Novell, until it gets notification from the application to release the locks it has placed on the LDB, it maintains them.  Once the offending owner has logged in and established that it has no locks on any files, and then logs out, the locked files are released, provided no other user logs in and starts using the app themselves.

Another problem which can cause lockouts is if transaction processing is being run and a problem occurs.  It is not the user which is leaving orphaned locks in the LDB file, it is Jet.  It is running the transaction process seperately from you running Access.  Even if there were no failures, older versions of Jet can leave orphaned locks in the LDB file.

The fix is the same thing.  Everybody must log out and you have to delete and purge the LDB before anyone can access or repair the data.

Therefore you should be sure that all workstations have the latest service releases of Access and the latest service pack for Jet.  How to get them can be found at http://www.experts-exchange.com/Computers/Databases/Access/Q.10217158.

There is an unsupported DLL, MSLDBUSR.DLL from Microsoft which allows you to determine which users have a database open.  It reads information directly out of the LDB file that jet uses to maintain locking information.  You need to declare access to the DLL and run an API call which returns the machine names of active users in a list box control.  You can pass parameters to the DLL asking for:

All users who had ever opened the database during this session.
All currently active users.
All users marked as corrupted.
The count of users.

This utility could be added to the database administrators functions when a problem occurs.  The information about this utility can be found at

http://support.microsoft.com/support/kb/articles/q186/3/04.asp

JimD:  Do you have "Jet Database Engine Programmer's Guide, Second Edition" by Microsoft Press?  There is several pages on exactly how the LDB works.  It appears to be quite contrary to the explanation that you gave.
0
 
psmith789Commented:
Make sure everyone is really out, then try to delete. If that won't do it, you may need to have your system administrator do it.

Once the .LDB file is gone, you may want to compact the database, and repair it if any problems show up.
0
 
JimMorganCommented:
The .LDB file is simply a database of what users have loaded the main database and what their locking status is.

Apparently, one user shut off their computer without shutting down Access first.

As psmith says, until everyone is really out, which might mean opening the DB under every users name and then doing a proper quit, the LDB will not go away.  I'm not even sure that a network administrator can delete a locked file.

Compact and repair have nothing to do with the LDB.  But if someone did a hard reboot or shutdown, then it is possible that the data in their internal buffers was not flushed out to the database and there are partial records hanging around.  Repair should remove those orphans.

Then the miscreant user will come back and complain about losing their data.

Jim
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
JimM

  Just a small correction on the above comments.   Repair does tie into LDB functionality.  

  If you do an abnormal disconnet and then delete the .LDB, it's still possible that the MDB is marked corrupt.

  The reason for this is that the "locking status" that you refer to is stored in the database header page and not the .LDB file.

  For each user slot in the LDB file, the DBH page has one 2 byte field that holds the status of the user.

So deleting the LDB file won't always do it.  Repair is sometimes required as well.

  pSmiths comments were correct.  The compact forces a check of the MDB and with the new compact utility, detects if a repair is required as well.  Up untill A95, MSFT always recommended a repair followed by a compact, but that is no longer true.  Now it's critical to do a compact first, then a repair, but only if prompted to do so by the compact process.

JimD.
0
 
chazzinpaAuthor Commented:
The problem I was having yesterday was that I couldn't delete the .LDB file or the .MDB file.  This morning, however, I was able to delete both.

Through trial and error, I discovered that bringing down my PC (logging off the network...Netware) is the only way that the lock gets released on these files.  I tried it a couple of times this morning and it seems to do the trick.

By the way, I also ran into problem which prevents me from being able to compact a database that is located on a server.  Once I was able to blow away the remote copies, I compacted the local version and copied them back to the server.

Thanks for all your input.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
JimM:

  Yes, I have the JET programmers guide and what I described is correct for the recovery process when an abnormal disconnect occurs.

  The LDB file does not contain the commit bytes.  The LDB file contains and array of 255 rows by 64 bytes (32 byte user name and a 32 bytes machine name).  Nothing else is stored in the LDB file and it will never be larger then 16K.

  Locks are placed on the LDB file for parts of the LDB file that don't exist (they are virtualized) by Access.  This is called extended byte range locking.  The OS or NOS handles these locks (on a local machine, it's SHARE or VSHARE).

  So when an abnormal disconnect occurs, you have several problems:

1.  The user slot in the LDB file is still filled.
2. Locks still exist on behalf of the user in the OS or NOS.
3.  The comitt bytes in the DBH are still set.

  To take care of step 1, you delete the .LDB file.  In most cases, you cannot do this untill you force log off the user or clear the open file, which takes care of step 2.

  Then depending on what the user was doing at the time of the disconnect, you may or may not need to run repair to clear the committ bytes.  However, MSFT now states that you should run a compact before any repair.  Certain types of index damange were caused by the reverse (remember in A95 the error "<value> is not an index in this table"?).

  So I can't see were any of my comments or psmiths comments were incorrect.  If you'd like, please give specific references and we can discuss them.

JimD.


0
 
JimMorganCommented:
I didn't say that anyone was incorrect.  BTW, I never worked with Access95.

I agree with all of your points and would like to continue the discussion but time has slipped away from me.  I started at 11:30 PM and here it is 5 AM and I have to go to work at 8 AM.  So I'm going to try to get 4 winks before the alarm goes off.

Nite, nite.

JimM
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
JimM:

  Go get some sleep! Goodnight<g> or I guess that should be good morning<g>?
0
 
chazzinpaAuthor Commented:
As I said in my original question, I am the user who was identified in the LDB as still being logged on to the database.  In fact, I was able to confirm this because I was already using MSLDBUSR.DLL as well as LDBVIEW.EXE.  However, logging in and out repeatedly never cleared the locks, hence the LDB file never got deleted.  

The only way I was able to resolve the issue, was by logging off the network.  No one mentioned that until I did.

I did try your link to Q.10217158, but got a URL not found message from Experts-Exchange.  Access 97 is at SR-2, however I don't know about Jet.

I didn't mean to imply that the compact problem was related to the LDB issue.  If you found the additional information confusing, I apologize.

In any event, thanks for all the info, Jim.  I hope you're catching up on your sleep.
0
 
JimMorganCommented:
I don't know why that URL is not working.  Must have the . out of whack.

I know that this one works, just checked it out.

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10217158 

I guess that I didn't catch that you were the culprit.  So after all this, the only logical answer was for you to log off Novell so Novell could release the locks that it had placed on the file.

Are you the network administrator as well?  The easiest solution in the future is to have the admin clear your account.  This resets all the flags held for you.  I think that if I had known earlier on that you were on Novell, then I would have been able to suggest that at the time.

Thanks.  I glad that now you have a better understanding of the situation.

Good for use, for using LDBVIEW.  I haven't had much of an opportunity to use it in a real live situation.  Our development server only has four people on it.  They are all within stapler throwing distance from me so I always can give a thump on the head and find out what everyone is doing.  :o

JimM

0
All Courses

From novice to tech pro — start learning today.