Database has one specific table causing lock issues

[jlederma]

I have an access DB which is a back end for a number of different front end DBs. We have been using it successfully for years with no real issues. In the last few weeks, we have been experiencing issues where that DB and the front end DBs will indicate that they are locked and in use by another user. As far as we have been able to determine, in 100% of these cases either 1 specific linked table is open in a front end DB or the backend DB is open (whether any tables, queries etc. are open or not). Front end DBs not actively accessing that specific table, but often accessing others in the same backend DB cause no issues at all.

I am at a loss. I can't identify anyway to address this issue at all. We tried compacting and repairing the backend db with no change. We made a copy of the offending table, renamed the old one and then gave the copy the original name and that didn't help. The backend DB is under 250MB and the table in question has a small number of fields and just over 14K records so I don't see any issues with size. The entire table is a single primary key autonumber field and a bunch of regular short text fields. There are no lookup fields, or any other advanced data types.

We work for a large healthcare company so we are not able to control windows/office updates at all.
Currently we are using Access 365 version 2406 (Build 17726.20206) and we are running windows 10 version 10.0.19045 Build 19045.

Do any of you have suggestions for fixing this?

 

[LarryE]

The first thing I would do is make sure all front-ends are closed and then look for any .laccdb ACCESS lock files anywhere in any users directories or in the common back-end directory. I am assuming:

1. Each user has their own copy of any of the front-end files on their own computer and are NOT sharing front-end files.
2. The common back-end file is located on a common shared directory, so the back-end directory can be accessed.

If you find any .laccdb files, when all the front-ends are closed, then delete them manually.

Once you know for certain that no .laccdb files are remaining on any drive when all front-ends are closed, then at least you can say it's not a file locking problem.

 

[gemma-the-husky]

Maybe someone is opening the problem database in a mode that secured sole access.

 

[jlederma]

Each user is opening their own copy of front end dbs, the back end is accessible on a network share to all and no one is opening the back end with secured access. Seriously, the issue ONLY happens if users access one specific back end table from their front ends. If they access any other table in the same backend database there are NO issues at all. It makes no sense to me.

 

[Pat Hartman]

We made a copy of the offending table, renamed the old one and then gave the copy the original name and that didn't help.

I hope you didn't have Change Auto Correct left on at the time. Check all your queries to see what table they are currently bound to. You may find that it is the original renamed table rather than the copy with the new name.

What, specifically, is the lock issue? Is it that the lock file doesn't get deleted when the last person closes his FE? Have you used one of the databases that shows who's logged in? Here is one:

Who's Logged On / Who's Connected

Description ... This utility will log the users who are connected to an MDB/E or ACCDB/E file via the UserRoster feature of the Jet/ACE database engine implemented with the introduction of Access 2000. In addition, the utility can manage the Connection Control (aka: Passive Shutdown) property...

www.access-programmers.co.uk