Table DROP Misfires

[Dave]

Programming in Acc2000/XP. I use the following code snippet to create 4
local tables:
sSQL = "SELECT * INTO " & sTableName
sSQL = sSQL & " FROM tblIndex " < tblIndex is replaced with tblDetail, etc
sSQL = sSQL & "WHERE FALSE;"
DoCmd.RunSQL sSQL, False

That is the basic code. There are 4 routines; each creating a different
table (copy of linked table structure). The 4 routines have the ability to
create 2 different sets of tables. I end up with 8 tables which are
temporary tables in a split db.

The program collects and processes the data and then ships it off to the db
on the network. When the user closes the form, part of the Unload procedure
is to delete (DROP) those tables. This is where the issue appears.

I have used the first line and the next 2 lines with similar results:
' DoCmd.RunSQL "DROP TABLE " & sTableName, False
DoCmd.SelectObject acTable, sTableName
DoCmd.DeleteObject acTable, sTableName

Both Index tables (tblLocal* and tblData*) remain regardless of the code
used, but the other tables are removed. There are no relationships
established in the relationship window, however queries have relationships
between tables; just not between tblLocalIndex and tblDataIndex. My VBA code
closes any recordset and sets the varaible to Nothing when exiting a routine
so it doesn't appear a reference remains.

Any suggestions are appreciated. Let me know if you need more information.

 

[Douglas J. Steele]

Try adding a reference to DAO (if you don't already have one), and using:

CurrentDb.TableDefs.Delete strTableName

You can also use the TransferDatabase method to create your tables: it has a
StructureOnly option.

 

[Dave]

Doug

I had to revise my error routines and place an error message in to discover
something is still using the table. So right now I'm tracing what is still
talking to the table. There are 2 combo boxes that did reference the 2
tables which I already changed from queries to RowSource values. The
tblDataIndex table now deletes upon exit, but the tblLocalIndex table is
still hanging. So the hunt continues. Thank you for the suggestions.

 

[Dave]

I had to clear the RowSource for the 2 comboboxes so the table could be
deleted. Delete command works fine as long as the RowSource doesn't point to
the table.

Thank you for the help.