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.
ClearTempTables procedure is a powerful tool that addresses this problem by simplifying the process of clearing out these local working tables.
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
- 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:
If you have any local tables whose data should not be deleted, be sure you update the function accordingly.
Here's how the
ClearTempTables procedure works, step by step:
- It first sets up a reference to the current database using the
- It then starts a loop over all table definitions in the database.
- For each table, it checks if the table is local (not linked), non-system (not starting with "MSys"), and non-internal (not starting with "~").
- It excludes specific tables named "YrMo" and "Changelog".
- It opens a recordset to count the number of records in the table.
- 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.
- Finally, it prints a completion message to the Immediate Window.
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
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.
- 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