CODEWITHSUNDEEP
ms-access
luntain
luntain

Reputation: 4680

How can I get table names from an MS Access Database?

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?

Upvotes: 40

Views: 154951

Answers (8)

John
John

Reputation: 4006

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.

Upvotes: 0

Jim
Jim

Reputation: 11

Best not to mess with msysObjects (IMHO).

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

Upvotes: 0

Ilya Kochetov
Ilya Kochetov

Reputation: 18443

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

Upvotes: 1

BIBD
BIBD

Reputation: 15384

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.

Upvotes: 62

Pete
Pete

Reputation: 17132

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

Upvotes: 8

Syed Daud
Syed Daud

Reputation: 11

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

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 11

onedaywhen
onedaywhen

Reputation: 57023

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

Upvotes: 2

Related Questions