ACC: How to Delete Multiple Tables Quickly (2.0, 95, 97)

ID: Q127978

The information in this article applies to:

• Microsoft Access version 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

In some situations, you may want to delete multiple tables quickly, rather than to delete them one at a time. For example, when you import data from another product, an error table may be created. After many imports, there may be several error tables to delete. This article shows you how to use Data Access Objects (DAO) to delete these multiple tables.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

The following example demonstrates how to use DAO in code to loop through the TableDefs collection and delete any tables whose name begins with "Import Errors."

1. Create a module and type the following line in the Declarations section

   if it is not already there:

      Option Explicit

      Function DeleteImportErrorTables ()
         Dim db As Database, t As TableDef, i As Integer
         Set db = CurrentDB()
         For i = db.tabledefs.count - 1 To 0 Step -1
            Set t = db.tabledefs(i)
            If t.name Like "Import Errors*" Then
               db.tabledefs.Delete t.name
            End If
         Next i
         db.Close
      End Function

   the Immediate window in version 2.0), and then press ENTER:

      ? DeleteImportErrorTables ()

   Any table whose name begins with "Import Errors" is deleted without
   confirmation.

REFERENCES

For more information about TableDef objects, search the Help Index for "TableDefs collection."

For information about deleting multiple tables in Microsoft Access version 1.x, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q106304
   TITLE     : ACC1x: How to Delete Multiple Tables Quickly (1.x)

Keywords          : kbusage
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 20, 1998