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

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

   <source lang="vb">

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

</source>
   
  

Using the OpenSchema Method to List Database Tables

   <source lang="vb">

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

</source>