access lock files on backend (1 Viewer)

[mikeco555]

I work for a company that runs an access database split into a front end and back end. This database works fine when used in a small environment(3 to 4 workstations), however when put into a larger situation(7-10 workstations) we seem to encounter many problems. These range from getting lock files on the backend database(.ldb), where we have to travel out to the site and manually delete these files(or everyone else get locked out and cannot use the system) to getting multiple error number 0(as I have written error handling into the program) to the point that it corrupts the backend and forces a repair. I guess I am just wondering if there are any tips anyone might have in dealing with these problems or it this just the limitations of the access environment.

Thanks,
Michael Oakes

 

[Pat Hartman]

If the users don't already have private copies of the fe on their c drives, do that. Frequent corruption and locking issues are usually related to users improperly exiting from the db. This can happen without their even knowing if they leave the db open for long periods but their PC is inactive causing it to "sleep". This temporarily disconnects the PC from the network and hence, disconnects Access. If this is happening, change the PC hardware settings to not power off or stop the hard drive. Search here for other possiblities.

 

[mdemarte]

multiple access lock files

About 2 weeks ago, we replaced a server at one of our sites. I went to update a program on Friday and discovered multiple lock files for my Access databases. Searched here, found this article. Halted that department from using databases while I researched the problem a bit more (worried about the corruption problem). Searched Microsoft's web-site, which also suggested a possible problem with permissions.

Spoke with my tech staff again, checked permissions. Turned out that users had add/change/etc., but did NOT have DELETE permission. (The tech had made a mistake when creating directory file structure and no one mentioned it to me. SIGH!) So, the user would open the database, the lock file would get created, the user would exit the database, the lock file would NOT get deleted. As far as I know, no error message was created. Since the databases were split, there was one lock file for the program and one for the data - some databases had one or two for each day, one had 100+ lock files! I had the tech update the permissions to include delete.

I copied each database to a safe place (including the lock files). Then, I deleted the lock files. Then I opened the database, closed the database and checked to be sure the lock files were getting deleted (which was working correctly now). Luckily, no data appeared to be corrupted. The users were unable to use their databases for only about half a day. It's now Monday, just checked and everything is still okay.

Wanted to add this to the database in the hopes of helping out someone out in the future.

 

[Pat Hartman]

I have never seen multiple lock files created for the same database. Access is supposed to use the existing one. So, if your users don't have delete permission, the .ldb will remain but it should be reused. A new one should not be created. How were the duplicates named?

 

[mdemarte]

For example, filenames were as follows:

hwydlpo;001.ldb
hwydlpo;002.ldb
hwydlpo;003.ldb
etc.

and

hwydlpo_be;001.ldb
hwydlpo_be;002.ldb
hwydlpo_be;003.ldb
etc.

As soon as I saw it, I KNEW something was wrong - but it took a bit to figure out what. In a weird way, it makes sense - since it could not delete the file, it created a new one.

 

[Pat Hartman]

No, it really doesn't make sense since the .ldb is ALWAYS supposed to be shared. That is the whole point of it. The .ldb keeps track of which user is doing what. I'm going to ask the other MVPs if they've ever seen this.