ACC2000: How to Delete Multiple Tables Quickly

• Microsoft Access 2000

SUMMARY

In some situations, you may want to delete multiple tables quickly, rather than 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. Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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." The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

1. Create a module and type the following line in the Declarations section if it is not already there:




Option Explicit
 

2. Type the following procedure:




Function DeleteImportErrorTables ()
Dim db As DAO.Database, t As DAO.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
 

3. To test this function, type the following line in the Immediate window, and then press ENTER:




? DeleteImportErrorTables () 


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

REFERENCES

For more information about Tabledefs, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "TableDefs Collection" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:

Keywords          : kbusage kbdta AccCon KbVBA 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 6, 1999