Cannot Delete Table

From: Bill Sturdevant (BillSturdevant_at_discussions.microsoft.com)
Date: 02/26/05 

Date: Sat, 26 Feb 2005 10:53:03 -0800

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?

Relevant Pages

  • Re: Cannot Delete Table
    ... Bill Sturdevant wrote: ... >During subsequent processing, I do the following: ... Is there a more appropriate to run an append query? ... The better way is to use the Execute method: ...
    (microsoft.public.access.formscoding)
  • Re: table lock / sharing issue
    ... When the append query is activated it seems the table cannot be set to "lock ... counter numbers and handling the concurrency and locking issues that ... also to avoid adversely affecting users who would just need to read ...
    (microsoft.public.access.tablesdbdesign)
  • Append query losing records when Access exited.
    ... Investigating why I can't access the table at the same time elsewhere, ... found the Append query has put a table lock in SQl, in such a way as when I ... key on the table.= and I have been using the Access.mdb and SQL database ...
    (microsoft.public.access.queries)