Access VBA delete Table using DoCmd.DeleteObject Method

This Access VBA tutorial explains how to delete Table (delete single table, delete multiple tables) in VBA using DoCmd.DeleteObject Method.

You may also want to read:

Access delete table records

Access VBA delete Table

In Access VBA, deleting Table can be done by DoCmd.DeleteObject Method. It is an extremely simple and straight forward, the syntax is as below. In order to delete Table, use acTable in the ObjectType argument.

DoCmd.DeleteObject(ObjectType, ObjectName)
Name Required/Optional Data Type Description
ObjectType Optional AcObjectType
Name Value Description
acDatabaseProperties 11 Database property
acDefault -1
acDiagram 8 Database Diagram (Microsoft Access project)
acForm 2 Form
acFunction 10 Function
acMacro 4 Macro
acModule 5 Module
acQuery 1 Query
acReport 3 Report
acServerView 7 Server View
acStoredProcedure 9 Stored Procedure (Microsoft Access project)
acTable 0 Table
acTableDataMacro 12 Data macro
ObjectName Optional Variant string expression that’s the valid name of an object of the type selected by the objecttype argument. If you run Visual Basic code containing the DeleteObject method in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.

 

Example 1 – Access VBA delete Table (delete single Table)

The below Macro deletes a Table called Table1. Unlike deleting Table manually, there is no alert to ask you to confirm deletion, meaning you don’t have to write extra code to disable alert.

Public Sub delTbl()
   DoCmd.DeleteObject acTable, "Table1"
End Sub

Example 2 – Access VBA delete Table (delete all Table)

The below Procedure will loop through all Tables and delete them. Click here to see my previous post for more detailed explanation.

Public Sub delTbl()
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Set db = CurrentDb

   For Each tdf In db.TableDefs
       If Not (tdf.Name Like "MSys*") Then
           DoCmd.DeleteObject acTable, tdf.Name
       End If
   Next
End Sub

Example 3 – Access VBA delete Table (delete specific Table)

In the above example, I use LIKE to exclude system tables from our deletion, because it would cause an error message to pop up. Similarly, we can use LIKE to to indicate what Table name to delete.

In the below example, all the Tables that have prefix Employee in the name will be deleted.

Public Sub delTbl()
 Dim db As DAO.Database
 Dim tdf As DAO.TableDef
 Set db = CurrentDb
 For Each tdf In db.TableDefs
    If Not (tdf.Name Like "MSys*") And tdf.Name Like "Employee*" Then
       DoCmd.DeleteObject acTable, tdf.Name
    End If
 Next
End Sub
Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Access VBA delete Table using DoCmd.DeleteObject Method

  1. hello, so i’ve got the beginning and the end. How can i execute a query for all tbl_temp ? the difficulty is to replace the table name in the query. I think i have to do a loop, but the code for all the steps is too difficult for me.

    Can you help me ?

    Have a good day.

Leave a Reply

Your email address will not be published. Required fields are marked *