Database could not LOCK table for update/delete queries??
Posted on 2008-11-19
If I try to update / make / delete a particular table I get the error, "The database engine could not lock table 'Table_Name' because it is already in use by another person or process."
When the update script runs (Docmd.Openquery) I first unbind all the controls by setting the recordsource to "".
But here's what I find weird:
If I close ALL the forms, and try to run the query from the object browser... I still get the error.
If I try to delete the table in the object broswer... I still get the error.
If I try to open the table, and manually manipulate it... I DO NOT get any error.
If I close the DB and re-open, there is no problem... until the script runs again. It's pretty basic.
Set warnings false
set warnings true
control.recordsource = "SELECT * from Table1"
control2.recordsource = "SELECT * from Table2"
It runs fine. The second time, the table locks up and I can't find a way to unlock it again.
Note that any recordsets I have in code are later set rst = nothing