Deleting temporary tables

[john]

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

[Mark Reed]

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" <jo**@test.comwrote in message
news:eZ********************@casema.nl...

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

[pietlinden]

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?

[Larry Linson]

"john" <jo**@test.comwrote

When leaving the main menu form I
want to delete some temporary tables.
. . .
But is it possible to change it so that
it deletes all tables starting with tmp?

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

[john]

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

"Mark Reed" <ma*********@ntlworld.comschreef in bericht
news:Zq*****************@newsfe7-win.ntli.net...

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" <jo**@test.comwrote in message
news:eZ********************@casema.nl...

>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