Cannot Delete Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I import some records into a temporary table "Temp_Products".

During subsequent processing, I do the following:
Set myTable = DB.OpenRecordset("Temp_Products")
---Do some processing---
myTable.Close
Set myTable = Nothing
and the table closes. I can then delete the table successfully.

Sometime during processing (but not while the table is already open as
above) I do the following:
DoCmd.OpenQuery "Import_Previously_Unrecorded_Products"
This is an append query that takes data from Temp_Products and puts it into
the appropriate permanent table.

Whenever I do the latter, at the end of the routine, when I try to delete
Temp_Products, I get error 3211 "The database engine could not lock table
'Temp_Products' because it is already in use by another person or process.

Since this is a single user system, I know it is not in use by another
person. The only thing I can think is that it is still in use somehow by the
query I ran.

How do I get around this? Is there a more appropriate to run an append query?
 
Bill said:
I import some records into a temporary table "Temp_Products".

During subsequent processing, I do the following:
Set myTable = DB.OpenRecordset("Temp_Products")
---Do some processing---
myTable.Close
Set myTable = Nothing
and the table closes. I can then delete the table successfully.

Sometime during processing (but not while the table is already open as
above) I do the following:
DoCmd.OpenQuery "Import_Previously_Unrecorded_Products"
This is an append query that takes data from Temp_Products and puts it into
the appropriate permanent table.

Whenever I do the latter, at the end of the routine, when I try to delete
Temp_Products, I get error 3211 "The database engine could not lock table
'Temp_Products' because it is already in use by another person or process.

Since this is a single user system, I know it is not in use by another
person. The only thing I can think is that it is still in use somehow by the
query I ran.

How do I get around this? Is there a more appropriate to run an append query?
Click to expand...


OpenQuery and (the more appropriate RunSQL) are executed as
separate (background) tasks so you don't know when it
finished.

The better (synchronous) way is to use the Execute method:

Dim db As DAO.Database
db.Execute "Import_Previously_Unrecorded_Products"

Depending on what you expect the query to do, you will
probably want to use the method's dbFailOnError argument.
See Help for details.
 
I adopted the suggested method, but somehow I still cannot delete the Temp
database. What code can I use to determine what process has the table open?
 
I don't think there is a way to enumerate the references to
a table. Some detective work may be required to determine
that. Are there any open forms/reports/queries that refer
to the table, possibly indirectly through a query.

One very tedious method to find where the table is being
used would be to rename the table. Then run your app and
each time you get an error because of the missing table, and
if it's a legimate reference, change the reference to the
new name. Eventually, you should find the offending
reference. Ugh!
 
Back
Top