File Locking (Access 2000)

**Steve_in_Kent** · 2003-10-23 08:49

I currently have a single back end access200 database, with two front ends.

Built into the two front ends is a link to a further backend with a single yes/no table, which interrogates this file every so often, if its set to yes, then it forces that front end to quit, and doesn't allow access to either front end anymore...

Usually what happens is the back end crashes, i kick everyone off, repair it, and off we go again, all hunky dory..

Unusually, whats happened today, is that one of the front ends, has crashed. I've kicked everyone off, but file locking is present on both the front end that i suspect has crashed, and the back end main db. (which i have confirmed is ok but going through the other front end)

So my question.. (and i suspect there is not a lot i can do), is how to get rid of the record locking, and gain exclusive access to it all again.

One of the things that i have considered, but discounted it, because this happens so rarely, and would involve a fair bit of work, is to register everyone's log in, when they log in, ie: their Windows network log in, to a file in the second back end. then log them out from this, as they leave the db. This would of course when it crashed, allow me to pinpoint the guilty person/ machine.

PS: can anyone recommend a database that has all the usability of Access, but none of its horrible limitations and bugs ?

**HansV** · 2003-10-23 08:55

Can you delete the .ldb file in the folders that contain the back end and front ends? You may have to log off and on again on each of the PC's involved, which may be a good idea anyway if Access crashed.

**Steve_in_Kent** · 2003-10-23 09:57

No Hans,. i tried that already. they are locked.

the trouble with logging off/on, is that there are too many Pc's with the front end on to track down an individual one.

roughly 80+ machines spread over a large site.. (although there are probably only 10-12 using this front end at any one time)

Hence if this happens again, i might have to log everyone's Windows log on name, in the second back end. but i really didn't want to go there.

**HansV** · 2003-10-23 10:04

I guess this is one of the good reasons to give every user a local copy of the front end database.

You can find out computer names of users logged in to a database, and even if those who left it in a suspect state, using code given in MSKB article HOW TO: Check Who Logged into Database with Jet UserRoster in Access 2000.

**charlotte** · 2003-10-23 12:07

I'm confused. You say there are two front ends with up to 80 machines using them, which implies front ends on the server. But you also say that there are too many PCs with the front end to track down an individual one. Are the front ends local to the individual PCs or not? And why two front ends? Which back end has the persistent lock, the one with the data or the one used to force logoffs?

The UserRoster approach Hans suggested can be a little tricky but it will tell you the machine name that has a connection to a particular database and, if you use Access security, the Access login. Otherwise, you'll get the machine name and Admin as the login. The Windows login is not part of the UserRoster data. It might be easier to just have your network administrator send a message to all those machines that have the database and require then to reboot their PC. That will remove an active hold on the database and the ldb file should then be deletable.

**WendellB** · 2003-10-23 12:28

As Charlotte suggests, you would be well advised to deploy the front-end to each user PC. For large sites like yours that can be a pain, but there are tools to help with it. FMS offers a commercial product (so do we), and there have been several posts in this forum about simple procedures to copy files to a PC. As to your prior question about databases, there are several competing products - though you won't get much help here on them. Actually for the number of users you are looking at, and the problems you apparently have with you network, you might find SQL Server as a back-end would give you better results. It seems to be much less affected by network problems.

**Steve_in_Kent** · 2003-10-23 12:52

Ahh yes Charlotte, i see what you mean, my terminology is not correct.

What i mean, is there are two front ends, that are linked to one back end. with around 15 users using one front end, and 80-100 users of the other front end. There are no local copies of the front end. The kick off option, kicks everyone off of both front ends. But, if a front end has crashed, it still keeps the file locking info.

Hans link to the micro$oft info is good, ive tried using it, but am getting a strange error saying i don't have the needed permissions. Even though i'm logged in as Admin. when i enter the strange error into the micro$oft site, i only found 1 link

http://support.microsoft.com/default.aspx?...kb;en-us;304070

And this link is talking about something completely different, although the error codes match exactly.

I have activated the Microsoft ActiveX Data Objects 2.5 Library.

**Support4John** · 2003-10-23 13:50

Hi Steve

1. When did your problems start?
2. Has your FE BE strategy ever worked properly?
3. Has any new HW SW been introduced into the environment?

I have never seen successful allowing multiple clients running off the same FE on a server. I use a lot of VBA with dynamic forms and reports that are driven by user table variables. The errors I use to receive never made any sense. After moving to individual FE for clients problems went away.

I know another developer at the same site that never has a problem allowing multiple clients running off the same FE on a server. However he doesn

**Steve_in_Kent** · 2003-10-24 06:32

Hi John,

The code in both of the front ends is full chocka with VBA, its all worked 'perfectly' for around 9-10 months.

The first front end, the one with 15 users, (Lets call it FE_A) is a mixture of around 8 users who have there own FE, and 7 occasional users who have a link to the FE on the server.

The second front end, (FE_[img]/forums/images/smilies/cool.gif[/img] had around 60-70 links that could potentially, use it, and just before problems started i added another 29.

Of the crashes that have happened previously, 99.95% of the time, its the back end. This is possibly only the 2nd time the FE_B has crashed.

I'm pretty sure, that if i could use the microsoft code to find who is still logged on, i could go shut the machine down, and repair the FE_B

However, that code is producing a weird error message as shown above. <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

My only other option, is to visit each and every machine to make sure its not open. however, believe it or not, that isn't an easy job, because they are scattered over a large area, and people could add the shortcut to their desktop from an email. ie: i can't be certain where all of them actually are. !!! <duh> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

**HansV** · 2003-10-24 06:55

Try this: download the Jet Utilities from ACC: Microsoft Jet Utilities Available in Download Center. It contains a demo application LDBUSER.EXE that can display a list of computer names logged in to a database. It is meant for Access 97 and earlier, but it works with Access 2000 and 2002 too.

**charlotte** · 2003-10-24 12:08

The caveat on that tool is that 2002 doesn't always create an ldb file on the backend. The ldb file gets created if you open an active connection to the back end, like opening a form bound to a table or running a query, but if only one user is in the database the ldb file is deleted when the operation is finished. Just having the front end open doesn't require an ldb on the back end.

Thread: File Locking (Access 2000)

Thread Tools