Creating a List of Database Tables(Types of tables in the ADOX Tables collection) : Table Type « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. 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

 

Related examples in the same category