Let’s say you have exported all your tables to another database and now want to delete them in current database. Unfortunately for security reason, Microsoft doesn’t facilitate this task by allowing you to select all tables and delete them all at once: if done manually you have to do it one by one.

The VBA function below will query the MsysObjects table to get the list of all tables (type = 1) in the current database:

'call deleteAllTables
Public Function deleteAllTables()

    Dim DB As Database
    Dim RS As Recordset
    Dim Table As String

    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("Select name from MsysObjects where (type = 1 and name not like 'Msys*')")

    Do While Not RS.EOF
        TableName = RS("Name")
        On Error Resume Next
        DoCmd.DeleteObject acTable, TableName
        RS.MoveNext
    Loop
    RS.Close
    MsgBox ("All Tables have been deleted")
End Function