Delete temp tables

[Bryan Hughes]

Hello,

I have some temp tables that I want to delete before my db closes.

I already have a OnLoad and OnUnlaod event that runs when the db first
opens.

On the UnLoad event I would like to search my db and delete any table that
ends with "Temp".

I have a sub that will do this but it is causing problems

Set dbs = CurrentDb()
dbs.TableDefs.Refresh
intTables = 0
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
strTableName = tdf.Name
If Right(strTableName, 4) = "Temp" Then
DoCmd.DeleteObject acTable, strTableName
End If
Next I
dbs.Close
Set tdf = Nothing
Set dbs = Nothing

I know there is a better way to do this can you please help?

-TFTH
Bryan

 

[Marshall Barton]

I have some temp tables that I want to delete before my db closes.

I already have a OnLoad and OnUnlaod event that runs when the db first
opens.

On the UnLoad event I would like to search my db and delete any table that
ends with "Temp".

I have a sub that will do this but it is causing problems

Set dbs = CurrentDb()
dbs.TableDefs.Refresh
intTables = 0
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
strTableName = tdf.Name
If Right(strTableName, 4) = "Temp" Then
DoCmd.DeleteObject acTable, strTableName
End If
Next I
dbs.Close
Set tdf = Nothing
Set dbs = Nothing

I know there is a better way to do this can you please help?

Don't forget the bloat issue associated with this approach.

A cleaner way to deal with temp tables is to create them in
a temp database. Either use the CreateDatabase method or
keep an empty "template" mdb and copy it. This allows you
to just Kill the temp db when you want to clean up. Here's
Tony's technique:
http://www.granite.ab.ca/access/temptables.htm

Of course, the best approach is to design your algorithms
without using temp tables ;-)