This code is for illustration purposes only. ALWAYS back up your database before you make changes.
We all use temporary tables in Access but we don’t always remember to clear them. You can write a query to clear each temp table after you use it, but why create extra objects and increase the size of your database container?
Instead, use some code that will run as you shut down the database. That way, your temporary tables will be empty before the database is compacted and repaired. (Hopefully you have Compact on Close set to true in your Options.)
Make a local table called tblTempTableNames with one field: TempTableName. Enter the name of each temporary table into tblTempTableNames. You can do this easily by right clicking the temporary table name, clicking Rename, using Control+C to copy, then right clicking an empty row in tblTempTableNames, and clicking Paste.
Now on your main menu (or whatever form has your Exit Access button) make a text box called txtTempTableName. This text box does not have to be visible. My form is called frmMainSwitchboard, so I will refer to my field as forms!frmMainSwitchboard!txtTempTableName.
Write the procedure:
Public Sub ClearAllTempTables
Dim rst as ADODB.Recordset
Dim mySQL as string
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType=adLockOptimistic
rst.Source = “tblTempTableNames”
rst.Open options:=adCmdTable
Do While Not rst.EOF
With rst
Forms!frmMainSwitchboard!txtTempTableName=rst![TempTableName].Value
Msgbox rst![TempTableName].Value (Used for testing – can be remmed out.)
mySQL = “DELETE FROM “ & forms!frmMainSwitchboard!txtTempTableName
DoCmd.RunSQL mySQL
rst.MoveNext
End With
Loop
rst.Close
Set rst=Nothing
End Sub
Compile your code.
On your Exit Access button, add the following two lines of code before the Exit.Application line:
On Error Resume Next
Call ClearAllTempTables
That’s it. When you exit Access, your temporary tables will be cleared of records, but the table structures themselves remain intact.
