ACC1x: How to Delete Multiple Tables Quickly

• Microsoft Access versions 1.0, 1.1

SUMMARY

In some situations you may want to quickly delete multiple tables, rather than deleting them one at a time.

For example, when you import data from another product, an error table may be created. After several imports, you may have several old import error tables to delete.

Below are two methods of quickly deleting multiple tables. The first method allows you to bypass the delete confirmation screen. The second method uses a function that deletes all tables that begin with "Import Errors," and is particularly useful when the same table type must be deleted often.

MORE INFORMATION

Method 1

You can bypass the "Delete table <tablename>?" delete confirmation screen by pressing SHIFT+DEL instead of just DEL.

For example, if you select a table named Test and then press DEL to delete the table, you will get the message:

   Delete table Test?

Method 2

To do a bulk deletion of tables with a certain name, you can use an Access Basic function. The sample function below is written specifically to delete import error tables. To delete other tables, you can change the name to the name of the tables you want to delete. The function must be called by an event such as a button press:

   Function DeleteImportErrorTables ()
      On Error GoTo ResetEnv

      Dim MyDB As Database, MySnapShot As Snapshot

      If MsgBox("Delete Import Error Tables?", 292) = 7 Then
         On Error GoTo 0
         Exit Function
      End If

      Set MyDB = CurrentDB()
      Set MySnapShot = MyDB.ListTables()

      DoCmd Echo False        ' Turn off screen updates.
      DoCmd SetWarnings False ' Turn off warning messages.

      Do Until MySnapShot.EOF
         If MySnapShot.[Name] Like "Import Errors*" Then
            DoCmd SelectObject A_TABLE, MySnapShot.Name, True
            DoCmd DoMenuItem A_DATABASE, A_EDIT, A_DELETE
         End If
         MySnapShot.MoveNext
      Loop

      DoCmd SetWarnings True ' Be sure to set SetWarning back on!
      DoCmd Echo True        ' Be sure to turn screen updating back on!

   Exit Function

   ResetEnv:

      DoCmd SetWarnings True ' Be sure to set SetWarning back on!
      DoCmd Echo True        ' Be sure to turn screen updating back on!
      Exit Function
   End Function