Access VBA loop through all Tables using DAO.TableDef

This Access VBA tutorial explains how to loop through all Tables using DAO.TableDef in Access.

You may also want to read:

Access VBA loop through Table records

Access VBA loop through all Tables

Let’s say we use a Macro to import 100 Tables and then we want to delete them all. It is time consuming for us to delete one by one and there is a high risk of manual error of deleting the wrong one. In this case we want to write a Macro to loop through all Tables to delete all Tables that meet certain criteria. Looping through Tables can be done with the help of DAO.TableDef, which is an objects that represents the stored definition of a base table or a linked table.

It is pretty standard to create the TableDef objects using the below lines of code. Just copy and paste every time you need to loop through Tables.

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

   For Each tdf In db.TableDefs
          'write your actions to the table
   Next
End Sub

Note that there are systems Tables that are hidden by default, those Tables have prefix MSys. We definitely want to avoid looping through those Tables, because it will cause an error message if we try to manipulate them. In order not to loop the system Tables, add the lines of code in red as below.

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
          'write your actions to the table
       End If
   Next
End Sub

Now you can write any action to perform on each Table. You may refer to my another post to demonstrate how to perform a Delete Tale Action.