Unable to delete a temporary table

[Amit]

MS Access 2000, Windows XP
======================

Hi,

I'm trying to delete a temporary table in the form Unload, but I get error
3211.
I've tried using the following two statements, and get the same error with
both.
1. DoCmd.DeleteObject acTable, "tmpTblMtg"
2. CurrentDb.TableDefs.Delete "tmpTblMtg"

I'm creating this temporary table when certain rows are selected in a list
on the form, and then running a query to display data in a second list on the
same form. The query uses the " .... WHERE GroupID IN (SELECT * FROM
tmpTblMtg)" clause to look up values in the temporary table.

When a button is clicked, I first check to see if the table already exists,
and if it doesn't, then I run the following:
DoCmd.RunSQL ("SELECT 0 AS GroupID INTO tmpTblMtg")
to create the table the first time.

If the table is already there, then I run the following:
DoCmd.RunSQL ("DELETE * FROM tmpTblMtg")

Following this, the selected values from the list are inserted into the
table using the following:
strInsertSQL = "INSERT INTO tmpTblMtg (GroupID) VALUES (" &
..ItemData(varItem) & ")"
Docmd.RunSQL strInsertSQL

Then, in the form's Unload event, I try to delete the table.

I've searched the Internet and the NG, but I haven't been able to find a
solution to why the table is not getting deleted. BTW, this is a stand-alone
database, and there is no other user who could lock the temp table (error
3211).

Ideally, I would like to delete this table when the form closes, but I could
try to delete it before the application quits. Or, if it doesn't work, then I
can live with having a temp table in the database. I'll simply delete all
rows when exiting.

Will appreciate any pointers in the right direction, or an explanation of
why the delete table statement is not working.

Thanks.

-Amit

 

[Stefan Hoffmann]

hi,

I'm trying to delete a temporary table in the form Unload, but I get error
3211.
I've tried using the following two statements, and get the same error with
both.
1. DoCmd.DeleteObject acTable, "tmpTblMtg"
2. CurrentDb.TableDefs.Delete "tmpTblMtg"

I never had problems with the second method.

Will appreciate any pointers in the right direction, or an explanation of
why the delete table statement is not working.

You are using your temporary table as record/row source for a control,
so you have to disconnect your control before deleting your temporary table.

Private Sub cmdConnect_Click()

CurrentDb.Execute "SELECT * INTO tmpTable FROM Table"
ListBox.RowSource = "tmpTable"

End Sub

Private Sub cmdDisconnect_Click()

ListBox.RowSource = ""
CurrentDb.TableDefs.Delete "tmpTable"

End Sub


mfG
--> stefan <--

 

[Amit]

Hi Stefan,

Yes, that was the issue. After I added the statement to make the rowsource
of the list "", I was able to delete the temp table in the form unload event.
Learn something new everyday

Thanks for your response.
Cheers,

-Amit