Delete all tables in one shot in Ms access

Question

Here is my VBA code, which tries to loop through all the tables which contains PB (PB is the first two letters of all of the tables) in their name and delete them. I get the error msg at DoCmd line

Option Compare Database
Option Explicit

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

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

Edit: I would like to not to delete the tables itself but delete the records inside the tables

Answer 1

Use DELETE action SQL to remove records. Use CurrentDb.Execute and don't have to worry about turning Warnings off and on.

CurrentDb.Execute "DELETE FROM [" & tdf.Name & "]".

If you want to delete records only from tables starting with PB, remove Not from the condition - otherwise you will delete from all other tables (including system tables which is bad).

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

   For Each tdf In db.TableDefs
       If tdf.Name Like "PB*" Then
           db.Execute "DELETE FROM [" & tdf.Name & "]"
       End If
   Next
End Sub