Deleting an Access lock file (1 Viewer)

kevlray

Registered User.
Local time
Today, 14:11
Joined
Apr 5, 2010
Messages
1,046
Unfortunately some staff members created an Access DB on a shared network drive, the dtatabase has linked tables to an MS-SQL database. At one point the database became corrupted (I was able to fix it by copying the DB to another directory and opening it from that location and saved it back). But the staff just wants that DB deleted (they have started using another copy of the database in another folder).

The issue is that there still is a locked Access file (.laccdb) that is in use by another application. We had the 'original' user whose name is associated with the lock file go into the Access DB and exit (I was hoping that it would delete itself). But alas, it is still there. Any suggestions on how to delete the lock file and the .accdb file? I also am in discussion with them to have each user have their own copy of the DB and not open it from the shared network drive.
 

isladogs

MVP / VIP
Local time
Today, 21:11
Joined
Jan 14, 2017
Messages
18,186
I also am in discussion with them to have each user have their own copy of the DB and not open it from the shared network drive.

This is absolutely VITAL to avoid corruption. No discussion should be involved.

I'm not clear why a corrupt SQL Server file should leave an Access LACCDB lock file.
Use Task Manager in DETAILED view to close all instances of Access on the relevant computer or on the server
This should remove any stray lock files.
If necessary, reboot first
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:11
Joined
Feb 19, 2002
Messages
42,976
I think it must be the FE that is corrupted.

I concur with Colin. Split ASAP. When multiple people open the same FE, think about what happens. Access loads code and objects into memory on the user's computer. At times, it needs to write back changes to the disk. For example user1 changes the sort order of a DS form and saves when he closes. Well that is going to update the form in the disk copy of the FE. What happens if user2 also had the form open at the same time? What happens if he tries to save his sort order?

There is nothing to negotiate. You are the expert. You tell them how to make this application more robust. To simplify distribution, create a .bat file that the users will have a shortcut to run. So instead of a shortcut to the FE or simply double clicking on the FE, they run the shortcut. The shortcut runs the .bat file and the bat file copies a fresh copy of the FE to the users C: drive. This takes a second to do so it doesn't slow down opening the app and it ensures that everyone has his own copy of the FE, the FE is always compacted, and if you changed the master copy, the user automatically gets the updated version.

Here's a copy of the one I use. It has only four statements.
1. md - to make the local directory if it doesn't exist
2. del - to delete an existing copy if one is there
3. copy - to copy the master to the local directory
4. run the app

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb
 

kevlray

Registered User.
Local time
Today, 14:11
Joined
Apr 5, 2010
Messages
1,046
Unfortunately I am just a grunt and have to make suggestions to our users ( I have no authority). But other IT staff were able to delete the offending MC-Access file.

But I have warned the staff involved with the database that the possibility of corruption is high because that they have multiple users in the front end of the database at any one time (on a Novell shared drive). The only good thing we have a pretty robust backup system in place. They may lose some data, but not the database.

I worked for months to split another database for one of our HR departments. They had about 25 users hitting this database and corrupting about every three days. They have not had a database corruption since it was split. The data now sits on a MS-SQL database.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:11
Joined
Feb 28, 2001
Messages
27,001
They may lose some data, but not the database.

No, no, NO! If you lose data, you might as well have lost the database. An unreliable database (that loses data) is a place nobody wants to put anything.

With or without authority, you have a boss who wanted this database to work correctly. HE has the authority. All you have to do is convince him that there is a right way and a very wrong way to share an Access database. Sharing the FE file among multiple users invites corruption through the process of "dangling file locks" that tie down parts of the Access file. By giving everyone their own copy of the FE file, you avoid lock conflicts, which conflicts (if they occur) would in theory lead to file deadlocks that would force BE server reboots at the most inconvenient times you could imagine.

If the database is of any importance to your business, you MUST be able to take proper steps to protect it. If it is of any importance to your boss, then HE must be ready to act to safeguard it. You might be only a grunt, but you are also a subject-matter expert. Explain things clearly to your boss that the path to data loss is:

Open shared FE >> lock conflicts >> conflicts leading to aborted sessions potentially holding other locks >> system deadlocks due to lock conflicts preventing the "lock release" code from executing.

The fix is to let each user have a private copy of the FE file, which means that they no longer share locks in the FE. All FE locks are now private. Further, it is possible to write the FE/BE communications to safely share and to take care of momentary conflicts.
 

Cronk

Registered User.
Local time
Tomorrow, 08:11
Joined
Jul 4, 2013
Messages
2,770
I'll make an additional suggestion. Do not allow users to open the FE from anywhere other than their local PC.

I have the code in the FE opening

If left(currentdb.name,1) <> "C" then
msgbox "you must run the db from your C drive"
application.quit
endif
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:11
Joined
Feb 28, 2001
Messages
27,001
I'm with Cronk here, although I added some logging code to a similar sequence because I wanted a record I could point to when I went to the boss and had him talk to folks about dangerous uses of the database. (Dangerous to data integrity, at least.)

But I eventually relented slightly because with the U.S. Government, sometimes they put strange rules on where "My Documents" will point on our government-issued systems. So I had to change the rules to say that you were not permitted to run the FE from the same disk as the BE, and that meant I had to look at the .Connect property of the linked tables to identify their locations.

Then some smart-aleck diddled with the shared folder so I added code that the FE path expressed as a string could not be a formal superset of the BE path - i.e. could not be part of the same directory tree. Much as we need them, sometimes I hate users for the hoops they bring into play for me to jump through them.
 

billpennock

Registered User.
Local time
Today, 14:11
Joined
Aug 10, 2018
Messages
10
All that maintaining of an FE on everyone's local machine is fine except when they decide to move it to the desktop, or move it off the desktop, or move it anywhere and use that one instead of the updated one. The "real" one that you think everyone is using is left in the designated folder and never touched. what is your way to handle that?
 

isladogs

MVP / VIP
Local time
Today, 21:11
Joined
Jan 14, 2017
Messages
18,186
I would handle that using code in an autoexec macro which checks the current project path. If that doesn't match the specified path, a message pops up to state the app can only be run from the correct location then the app quits.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:11
Joined
Sep 21, 2011
Messages
14,047
All that maintaining of an FE on everyone's local machine is fine except when they decide to move it to the desktop, or move it off the desktop, or move it anywhere and use that one instead of the updated one. The "real" one that you think everyone is using is left in the designated folder and never touched. what is your way to handle that?

Should use shortcuts as well.?
I've always given the user a shortcut on their desktop.
 

Cronk

Registered User.
Local time
Tomorrow, 08:11
Joined
Jul 4, 2013
Messages
2,770
In #7, I posted a code snippet that ensures the FE has to be run on the C drive.

I also have a version check so that when the user opens their local copy, its version is compared with the latest version number stored in the backend. When there is a difference in version number because of an update, a message is displayed to the user about running an old version and the current version is copied to the users' local drive.


If the user wants to move their local FE copy elsewhere, they will continue to get prompts about running an old version unless they move the new version to the "elsewhere" folder.
 

isladogs

MVP / VIP
Local time
Today, 21:11
Joined
Jan 14, 2017
Messages
18,186
Whilst I also always distribute shortcuts, if the app is moved to a new location, Windows will often (but not always) update the shortcut path as well.
 

Users who are viewing this thread

Top Bottom