How can I get table names from an MS Access Database

Question

Microsoft SQL Server and MySQL have an INFORMATION SCHEMA table that I can query  However it does not exist in an MS Access database   Is there an equivalent I can use

User · Accepted Answer

To build on Ilya s answer try the following query   SELECT MSysObjects Name AS table name FROM MSysObjects WHERE    Left  Name  1   lt  gt               AND   Left  Name  4   lt  gt  MSys            AND   MSysObjects Type  In  1 4 6    order by MSysObjects Name     this one works without modification with an MDB   ACCDB users may need to do something like this  SELECT MSysObjects Name AS table name FROM MSysObjects WHERE    Left  Name  1   lt  gt               AND   Left  Name  4   lt  gt  MSys            AND   MSysObjects Type  In  1 4 6           AND   MSysObjects Flags  0   order by MSysObjects Name    As there is an extra table is included that appears to be a system table of some sort

User · Answer

Getting a list of tables   SELECT      Table Name   Name   FROM      MSysObjects  WHERE       Left  Name  1  lt  gt           AND  Left  Name  4   lt  gt   MSys        AND   Type  In  1  4  6    ORDER BY      Name

User · Answer

Schema information which is designed to be very close to that of the SQL-92 INFORMATION SCHEMA may be obtained for the Jet ACE engine  which is what I assume you mean by  access   via the OLE DB providers    See   OpenSchema Method  ADO    Supported Schema Rowsets

User · Answer

SELECT  Name  FROM  MSysObjects  WHERE   Left  Name  1  lt  gt       AND  Left  Name  4   lt  gt   MSys    AND   Type  In  1  4  6    ORDER BY  Name

User · Answer

You can use schemas in Access   Sub ListAccessTables2 strDBPath     Dim cnnDB As ADODB Connection    Dim rstList As ADODB Recordset     Set cnnDB   New ADODB Connection       Open the connection     With cnnDB        Provider    Microsoft Jet OLEDB 4 0         Open strDBPath    End With       Open the tables schema rowset     Set rstList   cnnDB OpenSchema adSchemaTables        Loop through the results and print the      names and types in the Immediate pane     With rstList       Do While Not  EOF          If  Fields  TABLE TYPE    lt  gt   VIEW  Then             Debug Print  Fields  TABLE NAME    amp  vbTab  amp                    Fields  TABLE TYPE            End If           MoveNext       Loop    End With    cnnDB Close    Set cnnDB   Nothing End Sub   From  http   msdn microsoft com en-us library aa165325 office 10  aspx

User · Answer

You can use schemas in Access   Sub ListAccessTables2 strDBPath     Dim cnnDB As ADODB Connection    Dim rstList As ADODB Recordset     Set cnnDB   New ADODB Connection       Open the connection     With cnnDB        Provider    Microsoft Jet OLEDB 4 0         Open strDBPath    End With       Open the tables schema rowset     Set rstList   cnnDB OpenSchema adSchemaTables        Loop through the results and print the      names and types in the Immediate pane     With rstList       Do While Not  EOF          If  Fields  TABLE TYPE    lt  gt   VIEW  Then             Debug Print  Fields  TABLE NAME    amp  vbTab  amp                    Fields  TABLE TYPE            End If           MoveNext       Loop    End With    cnnDB Close    Set cnnDB   Nothing End Sub   From  http   msdn microsoft com en-us library aa165325 office 10  aspx

User · Answer

Getting a list of tables   SELECT      Table Name   Name   FROM      MSysObjects  WHERE       Left  Name  1  lt  gt           AND  Left  Name  4   lt  gt   MSys        AND   Type  In  1  4  6    ORDER BY      Name

User · Answer

Here is an updated answer which works in Access 2010 VBA using Data Access Objects  DAO   The table s name is held in TableDef Name   The collection of all table definitions is held in TableDefs   Here is a quick example of looping through the table names   Dim db as Database Dim td as TableDef Set db   CurrentDb   For Each td In db TableDefs   YourSubTakingTableName td Name  Next td

User · Answer

Getting a list of tables   SELECT      Table Name   Name   FROM      MSysObjects  WHERE       Left  Name  1  lt  gt           AND  Left  Name  4   lt  gt   MSys        AND   Type  In  1  4  6    ORDER BY      Name

User · Answer

Best not to mess with msysObjects  IMHO    CurrentDB TableDefs CurrentDB QueryDefs CurrentProject AllForms CurrentProject AllReports CurrentProject AllMacros

User · Answer

Here is an updated answer which works in Access 2010 VBA using Data Access Objects  DAO   The table s name is held in TableDef Name   The collection of all table definitions is held in TableDefs   Here is a quick example of looping through the table names   Dim db as Database Dim td as TableDef Set db   CurrentDb   For Each td In db TableDefs   YourSubTakingTableName td Name  Next td

User · Answer

Schema information which is designed to be very close to that of the SQL-92 INFORMATION SCHEMA may be obtained for the Jet ACE engine  which is what I assume you mean by  access   via the OLE DB providers    See   OpenSchema Method  ADO    Supported Schema Rowsets

User · Answer

SELECT  Name  FROM  MSysObjects  WHERE   Left  Name  1  lt  gt       AND  Left  Name  4   lt  gt   MSys    AND   Type  In  1  4  6    ORDER BY  Name

User · Answer

Best not to mess with msysObjects  IMHO    CurrentDB TableDefs CurrentDB QueryDefs CurrentProject AllForms CurrentProject AllReports CurrentProject AllMacros

User · Answer

Schema information which is designed to be very close to that of the SQL-92 INFORMATION SCHEMA may be obtained for the Jet ACE engine  which is what I assume you mean by  access   via the OLE DB providers    See   OpenSchema Method  ADO    Supported Schema Rowsets

User · Answer

Schema information which is designed to be very close to that of the SQL-92 INFORMATION SCHEMA may be obtained for the Jet ACE engine  which is what I assume you mean by  access   via the OLE DB providers    See   OpenSchema Method  ADO    Supported Schema Rowsets

User · Answer

You can use schemas in Access   Sub ListAccessTables2 strDBPath     Dim cnnDB As ADODB Connection    Dim rstList As ADODB Recordset     Set cnnDB   New ADODB Connection       Open the connection     With cnnDB        Provider    Microsoft Jet OLEDB 4 0         Open strDBPath    End With       Open the tables schema rowset     Set rstList   cnnDB OpenSchema adSchemaTables        Loop through the results and print the      names and types in the Immediate pane     With rstList       Do While Not  EOF          If  Fields  TABLE TYPE    lt  gt   VIEW  Then             Debug Print  Fields  TABLE NAME    amp  vbTab  amp                    Fields  TABLE TYPE            End If           MoveNext       Loop    End With    cnnDB Close    Set cnnDB   Nothing End Sub   From  http   msdn microsoft com en-us library aa165325 office 10  aspx

User · Answer

Getting a list of tables   SELECT      Table Name   Name   FROM      MSysObjects  WHERE       Left  Name  1  lt  gt           AND  Left  Name  4   lt  gt   MSys        AND   Type  In  1  4  6    ORDER BY      Name

User · Answer

You can use schemas in Access   Sub ListAccessTables2 strDBPath     Dim cnnDB As ADODB Connection    Dim rstList As ADODB Recordset     Set cnnDB   New ADODB Connection       Open the connection     With cnnDB        Provider    Microsoft Jet OLEDB 4 0         Open strDBPath    End With       Open the tables schema rowset     Set rstList   cnnDB OpenSchema adSchemaTables        Loop through the results and print the      names and types in the Immediate pane     With rstList       Do While Not  EOF          If  Fields  TABLE TYPE    lt  gt   VIEW  Then             Debug Print  Fields  TABLE NAME    amp  vbTab  amp                    Fields  TABLE TYPE            End If           MoveNext       Loop    End With    cnnDB Close    Set cnnDB   Nothing End Sub   From  http   msdn microsoft com en-us library aa165325 office 10  aspx

[ms-access] How can I get table names from an MS Access Database?

Examples related to ms-access