[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1326
  • Last Modified:

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

0
NO_CARRIER
Asked:
NO_CARRIER
  • 2
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
NO_CARRIERAuthor Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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 would presume this is because you've still got something on the form that is locking the table, or else your other queries/processes have not yet finished. Are these linked tables? If so, are they linked to an Access database? Linked tables behave differently than do local tables, and you may not be able to delete a linked table.

Also, I'm not sure why you'd need to re-create the table (i.e. use a make table query). Typically you'd just re-insert the data into an existing table ...

As to why you cannot delete the table - if you have absolutely no other forms/reports open (even a switchboard) and you cannot delete a table (i.e. highlight a table and press the Delete key), then I'd suspect you have issues with your database (i.e. corruption or something like that).

Finally, unbinding a form is fine, but Access may not release that lock. I don't see any reason as to why you are processing your data in this fashion, so can't comment on that, but when using bound forms (and you're using bound forms, regardless of what you're doing with the recordsource) you must adhere to the concepts of those. Merely setting the recordsource to "" may not fully unbind your form, and if you have combos or listboxes with a RowSource set, Access will generate locks for those as well.
0
 
NO_CARRIERAuthor Commented:
It turned out that when a report is generated for output to a file, it was remianing HIDDEN in Access as a report.  The only way to see the report is to go to Window, then Unhide.  It had to be programatically closed just in case it existed.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now