Automating Temporary Data Cleanup in Access with the ClearTempTables() Procedure

You should only store permanent data in a local table if it meets four criteria:

• The data is read-only
• The data cannot be joined to back-end production tables
• There are fewer than 500 records in the table
• The data rarely (if ever) changes

Any other type of permanent data should be stored in the back-end database, whether that is Access, SQL Server, MySQL, or something else.

Storing Temporary Data in the Front End

Notwithstanding those few tables with permanent data that meet the above criteria, most local tables in a front-end Access application hold temporary data.

These tables can be a blessing, providing a convenient way to store intermediate results during complex operations. However, they can also be a curse, cluttering our databases with obsolete data and consuming valuable storage space.

The ClearTempTables procedure is a powerful tool that addresses this problem by simplifying the process of clearing out these local working tables.

The Approach

The ClearTempTables procedure leverages the DAO (Data Access Objects) library to interact with the database and its tables.

It loops through each table in the database, identifies local, non-system, non-internal tables, and then deletes the data from those tables if they contain more than 500 records.

All data is deleted from tables that meet the following criteria:

• Local Table: has a zero-length .Connect property
• Non-System Table: table name does not begin with "MSys"
• Non-Internal Table: table name does not begin with tilde (~)
• Table Has Over 500 Rows: this helps avoid deleting small lookup tables or application configuration tables whose data should not be cleared
• Table Does Not Match Exclusion List: any local table with more than 500 rows of permanent data should be explicitly exempt from this function

This approach helps avoid accidentally deleting important data, but you must understand how the routine works before using it in your own applications.  At a minimum, you should review the exclusion list and update it to match your environment.  As is, the routine will avoid deleting tables named:

• YrMo
• Changelog

If you have any local tables whose data should not be deleted, be sure you update the function accordingly.

The Algorithm

Here's how the ClearTempTables procedure works, step by step:

1. It first sets up a reference to the current database using the CurrentDb function.
2. It then starts a loop over all table definitions in the database.
3. For each table, it checks if the table is local (not linked), non-system (not starting with "MSys"), and non-internal (not starting with "~").
4. It excludes specific tables named "YrMo" and "Changelog".
5. It opens a recordset to count the number of records in the table.
6. If the table contains more than 500 records, it prints the table name and record count to the Immediate Window (for debugging purposes) and then executes a DELETE SQL statement to remove all records from the table.
7. Finally, it prints a completion message to the Immediate Window.

The Function

Here is the ClearTempTables() function.  It has no dependencies, other than requiring a reference to DAO.

' ----------------------------------------------------------------
' Procedure : ClearTempTables
' Date      : 10/27/2023
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/cleartemptables/
' Purpose   : Automatically empties out local working tables.
' Notes     - BE CAREFUL!!! This routine deletes data with no warning.
'           - It will NOT delete any data in linked tables.
'           - This routine should only be run on the front-end of a split database.
'           - Be sure you understand what this routine does before you run it.
' ----------------------------------------------------------------
Sub ClearTempTables()
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Dim td As DAO.TableDef
    For Each td In db.TableDefs
        'Get local, non-system, non-internal tables...
        If td.Connect = "" And Left(td.Name, 4) <> "MSys" And Left(td.Name, 1) <> "~" Then
            '...excluding these ones...
            If td.Name <> "YrMo" And td.Name <> "Changelog" Then
                '...with lots of records
                Dim Recs As Long
                Recs = db.OpenRecordset("SELECT Count(*) AS Num FROM [" & td.Name & "]")("Num")
                If Recs > 500 Then
                    Debug.Print "  " & td.Name & "  " & Recs: DoEvents
                    'And clear them out
                    db.Execute "DELETE * FROM [" & td.Name & "]"
                End If
            End If
        End If
    Next
    
    Debug.Print "ClearTempTables complete."
End Sub

Sample Usage

I generally run this function from the immediate window or by placing my cursor in the procedure and pressing [F5].  This is one of the last things I do when preparing my front-end for deployment.

Acknowledgements

• Portions of this article's body generated with the help of ChatGPT
• Sample data generated with the help of ChatGPT

Cover image created with Microsoft Designer