Deleting temporary tables

When leaving the main menu form I want to delete some temporary tables. The
following code on the form's close works: DoCmd.DeleteObject acTable,
"tmpEnvelop". But is it possible to change it so that it deletes all tables
starting with tmp?
Thanks,
john

re: Deleting temporary tables

How I would do this is to create a new module and paste the following:

'**************Code Start***************
Sub Delete_Temp_Tables()

Dim Rst As DAO.Recordset
Dim StrSql As String

'Create a recordset containing table names beginning with 'TMP'
StrSql = "SELECT MSysObjects.Name " & _
"FROM MSysObjects " & _
"WHERE (((MSysObjects.ParentId) = 251658241)) " & _
"GROUP BY MSysObjects.Name " & _
"HAVING (((MSysObjects.Name) Like 'tmp*'));"

Set Rst = CurrentDb.OpenRecordset(StrSql, , dbReadOnly)

With Rst
Do While Not .EOF
DoCmd.DeleteObject acTable, !Name
.MoveNext
Loop
End With

Rst.Close
End Sub
'**************Code End***************

Then on the 'Unload' event of your main menu form, simply enter:
'Delete_Temp_Tables' without the quotes.

Hope this helps,

Mark


"john" <john@test.comwrote in message
news:eZqdnT_yp6v9AGTZRVnytQ@casema.nl...

re: Deleting temporary tables

why would you delete the temp tables entirely? Why not just truncate
them all? or even just put them in an external DB and then delete the
external DB when you're done with it?

re: Deleting temporary tables

"john" <john@test.comwrote
You can avoid the need for frequent Compact and Repair if you'll define your
temporary Tables in a temporary Database. There's an example at MVP Tony
Toews' site, http://www.granite.ab.ca/accsmstr.htm. Then when you are done,
you unlink, and delete the entire temporary Database.

Larry Linson
Microsoft Access MVP

re: Deleting temporary tables

Thans Mark and others,
Didn't know you could query on table names like that.
john

"Mark Reed" <mark.reed75@ntlworld.comschreef in bericht
news:ZqiKg.8511$H11.2629@newsfe7-win.ntli.net...