NO_CARRIER
asked on
Database could not LOCK table for update/delete queries??
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.
control.recordsource=""
control2.recordsource=""
Set warnings false
OpenQuery "stuff1"
OpenQuery "stuff2"
OpenQuery "stuff3"
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
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.
control.recordsource=""
control2.recordsource=""
Set warnings false
OpenQuery "stuff1"
OpenQuery "stuff2"
OpenQuery "stuff3"
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
ASKER
What do your queries do? Are you destroying and recreating tables (your first sentence seems to indicate this)?
The error you're getting is typically caused by writing data via VBA or queries when you have a form open to the same records ... so if your form is viewing/editing Record1, and you try to manipualte Record1 via code you'll get this error. If you're using bound forms, then you should do all data manipulation on the form.
I'm not clear what you mean by "unbind all controls by setting the recordsource to """. Can you explain?
There are some listboxes that are bound to the form.
The queries (around 15 of them) run in succession to import data from various sources, chop it and organize it for the final bound table.
Before the queries run to update the tables (via VBA), I set the source for the bound controls to "". When the queries are done executing, I change the source back to the table. (i.e. SELECT * from Final_Table Where Total > 15)
When it gets to the last MAKETABLE query to re-create the final_table. It says it can't because the table is locked.
I can not do a make table query. I can do perform a delete query.
However, if I do an APPEND or UPDATE query on the table, it works fine.
The part that I really don't understand is, why is it if I close all the forms, THEN try to delete the table or just run a make-table query from the object browser.. the table is STILL locked? What can it possibly be bound to?
Note that I'm the only person using this database, and I'm nowhere near the 2GB database limit.
The error you're getting is typically caused by writing data via VBA or queries when you have a form open to the same records ... so if your form is viewing/editing Record1, and you try to manipualte Record1 via code you'll get this error. If you're using bound forms, then you should do all data manipulation on the form.
I'm not clear what you mean by "unbind all controls by setting the recordsource to """. Can you explain?
There are some listboxes that are bound to the form.
The queries (around 15 of them) run in succession to import data from various sources, chop it and organize it for the final bound table.
Before the queries run to update the tables (via VBA), I set the source for the bound controls to "". When the queries are done executing, I change the source back to the table. (i.e. SELECT * from Final_Table Where Total > 15)
When it gets to the last MAKETABLE query to re-create the final_table. It says it can't because the table is locked.
I can not do a make table query. I can do perform a delete query.
However, if I do an APPEND or UPDATE query on the table, it works fine.
The part that I really don't understand is, why is it if I close all the forms, THEN try to delete the table or just run a make-table query from the object browser.. the table is STILL locked? What can it possibly be bound to?
Note that I'm the only person using this database, and I'm nowhere near the 2GB database limit.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The error you're getting is typically caused by writing data via VBA or queries when you have a form open to the same records ... so if your form is viewing/editing Record1, and you try to manipualte Record1 via code you'll get this error. If you're using bound forms, then you should do all data manipulation on the form.
I'm not clear what you mean by "unbind all controls by setting the recordsource to """. Can you explain?