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?

Answer 1

To build on Ilya's answer try the following query:

SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>"~")          AND ((Left([Name],4))<>"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))<>"~")          AND ((Left([Name],4))<>"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.

Answer 2

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") <> "VIEW" Then             Debug.Print .Fields("TABLE_NAME") & vbTab & _                .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