Access: Locked Table for Editing

[ChrisJohn]

Morning

I have a table which is linked to a form which has been working fine for over a year now but yesterday the table "locked" for editing. I can now only copy data from the table. The editing functions delete, cut, new etc are not working, we can still filter the data.

If we enter data on the form it creates a new line on the table but we cannot edit directly from the table.



Any help would be much appreciated.

Thank you.

[Evilferret]

Before you begin, close access and make a copy of your database for backup (In case you accidentally break something while trying to fix it)

Steps to try (Check if the problem is fixed after each step):

(1) Ensure that the database isn't already open on another computer on your network, and that you don't have another copy of access already running. The table may actually be in use somewhere else

(2) In access try running a "Compact and Repair" on the database. This fixes many common problems and might resolve your issue.

(3) Using a SQL Query, try doing a rollback or a commit on the table that's locked.

(4) (If all else fails) Create a new table with the same fields. Write a SQL query to get the data from the old table and put it into the new table. If your original table allowed for deletions and had a primary key, this can be a bit tricky.

[JoeM]

Also check for a "hung" database locking file (in the directory where the database exists, you will see either a file with the same name but a "ldb" or "lccdb" extension).
If you are sure everyone is out of the database, but you see one of these lock files, try to delete it. If you cannot, you may need to kill it on the computer it became locked on, then delete the lock file.

We have scheduled jobs that run form a server and hang like this occassionally. I can get away without having to re-boot the server by going in to the Task Manager and killing the MSACCESS.exe process that is running, which then allows me to delete the laccdb file.

If all else fails, you may need to re-boot your server to release the lock.

[kandakke]

Hi JoeM,

I'm having the problem you described with the "hung" database locking file. The file-type is "Microsoft Access Record-Locking Information". This errant record-locking file is causing a nasty error message that I don't want the users of my Access application to see. Obviously I can close the Access program to delete the file, but I'd like to find a work-around programmatically if possible. I've been searching this forum for several hours and this was the first thread that described my situation.

Has anyone found a programmatic solution to this problem? Is there a way I can force access to release the lock on the table I need? It's a temporary table too, which I thought from this documentation shouldn't even care about locks... evidently they do.

Thanks for any pointers!

[JoeM]

I'm having the problem you described with the "hung" database locking file.

The better question to ask, is why this keeps happening? If you can address the issue at the source, you may be able to avoid/eliminate the problem altogether.
Here are some key questions to ask:
- Do you have multiple users using the database?
- Do they all have the same version of Access on their computer?
- Have you "split" the database?
- Do your users all close the database when they are finished with it?
- Is your database corrupt? If so, have you tried importing it into a new database?
- Does your database connect to any outside data sources?

I have found that in multi-user environments, splitting the database makes the biggest impact on avoiding the problem.

[kandakke]

Thanks for your reply, JoeM.

I have resolved this problem by removing the button that was causing the error (it only happened when navigating from one particular area directly to the problem button). There is another way to get to the target area, so removing the button was the easiest thing to do after much searching and no other solutions appearing.

To answer your questions anyway for other readers:
No, multiple users are not using the database - it's just me working on it. When I'm done a change / update, I copy it over to the "user's" computer.
(Yes, I have the same version of Access as the "user").
I have split the database.
Corrupt? Hm, there could be something here...When you say "importing it into a new database", do you mean importing the main application or importing the "split" database tables file? Or both? It may not matter however, since I've resolved the issue, but good to know should I encounter this issue again.
No, my database doesn't connect to any outside data sources.

Thanks for the advice on splitting. I inherited it that way, so I've continued working on it that way (and I understand that it's better that way).

[JoeM]

Corrupt? Hm, there could be something here...When you say "importing it into a new database", do you mean importing the main application or importing the "split" database tables file? Or both? It may not matter however, since I've resolved the issue, but good to know should I encounter this issue again.

It would depend on which half was corrupt. I think the front-end would typically be more likely to be corrupt, as that where most if the programmability occurs, but either one could potentially become corrupt.
To correct this, you create a new blank database, and import all the objects from the old one into the new one (using Import -> Access Database... and select all the objects).