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?

Hawes

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.

Realism

Record(s) cannot be read; no read permission on 'MSysObjects'. – Harmless

That getSchema article is no longer present. – Layby

There are several answers on this page, so why wayback? And why c#? – Threedecker

Why WayBack - that's how I found the original content after the blog owner of the link wiped his blog and restarted it. Why c#? That's what the blog owner and the person who edited my post chose to do a year ago. I was just fixing a broken link. – Realism

You're right... I just tested hype8912's edit, and he was wrong. It does work in 2007. Reverted! – Realism

@PauAI - it appears to for me. although there' appears to be an extra system table included, I've updated the answer with a fix for that. – Realism

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

Threedecker

Answer 3

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

Roshan

Answer 4

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

Ashy

Answer 5

Getting a list of tables:

SELECT 
    Table_Name = Name, 
FROM 
    MSysObjects 
WHERE 
    (Left([Name],1)<>"~") 
    AND (Left([Name],4) <> "MSys") 
    AND ([Type] In (1, 4, 6)) 
ORDER BY 
    Name

Draught

Works in old Access versions only. – Layby

MsysObjects are unsupported system tables and should not be used. Permissions on these tables outside of MS Access can be very chancy. – Threedecker

Answer 6

SELECT 
Name 
FROM 
MSysObjects 
WHERE 
(Left([Name],1)<>"~") 
AND (Left([Name],4) <> "MSys") 
AND ([Type] In (1, 4, 6)) 
ORDER BY 
Name

Superdominant

MsysObjects are unsupported system tables and should not be used. Permissions on these tables outside of MS Access can be very chancy – Threedecker

Answer 7

Best not to mess with msysObjects (IMHO).

CurrentDB.TableDefs
CurrentDB.QueryDefs
CurrentProject.AllForms
CurrentProject.AllReports
CurrentProject.AllMacros

Mllly

Answer 8

I needed to slightly modify the SQL posted by BIBD (needed to fully quality the table name by adding sys. to MSysObjects in the from clause.

String sqlString = "";
sqlString += "SELECT MSysObjects.Name AS table_name \n";
sqlString += "FROM sys.MSysObjects \n";
sqlString += "WHERE (((Left([Name],1))<>\"~\")  \n";
sqlString += "        AND ((Left([Name],4))<>\"MSys\")  \n";
sqlString += "        AND ((MSysObjects.Type) In (1,4,6)) \n";
sqlString += "        AND ((MSysObjects.Flags)=0)) \n";
sqlString += "order by MSysObjects.Name \n";

A full working example is available at https://github.com/NACHC-CAD/access-to-csv-tool. This example also shows connecting to an MS Access database using jdbc and exporting all tables as csv using Apache Commons CSV.

Indiraindirect