VBA/Excel/Access/Word/Access/Table Type

Creating a List of Database Tables(Types of tables in the ADOX Tables collection)

 
Name               Description
ACCESS TABLE       An Access system table
LINK               A linked table from a non-ODBC data source
PASS-THROUGH       A linked table from an ODBC data source
SYSTEM TABLE       A Microsoft Jet system table
TABLE              A Microsoft Access table
VIEW               A table from a row-returning, non-parameterized query
Sub ListTbls()
   Dim cat As ADOX.Catalog
   Dim myTable As ADOX.Table
   Set cat = New ADOX.Catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & CurrentProject.Path & _
       "\mydb.mdb"
   For Each myTable In cat.Tables
      If myTable.Type <> "VIEW" And _
          myTable.Type <> "SYSTEM TABLE" And _
          myTable.Type <> "ACCESS TABLE" Then Debug.Print myTable.Name
   Next myTable
   Set cat = Nothing
   MsgBox "View the list of tables in the Immediate window."
End Sub

Using the OpenSchema Method to List Database Tables

 
Sub ListTbls2()
   Dim rst As ADODB.Recordset
   Set rst = CurrentProject.Connection.OpenSchema(adSchemaTables)
   Do Until rst.EOF
      Debug.Print rst.Fields("TABLE_TYPE") & " ->" _
          & rst.Fields("TABLE_NAME")
      rst.MoveNext
   Loop
End Sub