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 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.
|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