Tip 95: Deleting All Records from Every Table in a Microsoft Access Database

May 22, 1995

Abstract

A Microsoft® Access® database may contain several tables, with each table holding many records. This article explains how to delete all records from all tables associated with a Microsoft Access database application.

Removing Records from Tables

There may be occasions when you need to delete all records in a table from a Microsoft® Access® database application. For instance, if you have an inventory program that contains a table of stock and another table of purchase orders, at the end of the year you would need to remove these records in preparation for the next year's information. To remove each record from both tables would be a tedious job.

You can create an Access Basic function that will remove every record from the specified table. However, if your Microsoft Access database contains many tables, you need to process each individual table associated with that specific Microsoft Access database.

Example Program

This example Access program shows how to delete all records from every table in a Microsoft Access database application. Note that this function also processes those table names that include space characters in them (Inventory Year1, for example).

1. Open the sample database ORDERS.MDB. (This database can usually be found in the C:\ACCESS\SAMPAPPS directory.)

   Note: The example program will permanently modify this Access database. Therefore, you should copy ORDERS.MDB to another directory and run this program on the temporary copy of ORDERS.MDB.

2. From the File menu, choose New, and select Module.
   
   
3. Enter the following code to create the DeleteAllRecords() function:

   Function DeleteAllRecords ()
   Dim DB As Database
   Dim X As Integer
   Dim TDF As TableDef
   DoCmd SetWarnings False
   Set DB = CurrentDB()
   For X = 0 To DB.TableDefs.Count - 1
   Set TDF = DB.TableDefs(X)
   If (TDF.Attributes And DB_SYSTEMOBJECT) = 0 Then
   DB.Execute "Delete * From [" & DB.TableDefs(X).Name & "]"
   End If
   Next X
   DoCmd SetWarnings True
   End Function
   

4. From the View menu, choose Immediate Window.
   
   
5. In the Immediate Window, type the following line and press the ENTER key:

   ?DeleteAllRecords()
   

   This statement will execute the DeleteAllRecords() function. After a short time, all records will be deleted from each table included in the ORDERS.MDB database.