By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Deleting temporary tables

P: n/a
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
Sep 2 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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


Sep 2 '06 #2

P: n/a
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?

Sep 2 '06 #3

P: n/a
"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
Sep 2 '06 #4

P: n/a
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



Sep 3 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.