Generate list of Microsoft Access database tables

How do I have Microsoft Access generate a list of Database tables? I came across an SQL query someone suggested on anther website however Access doesn't seem to have any interface to simply allow running a user-constructed query such as the one below.

SELECT [Name] FROM MSysObjects WHERE Type In (1,4,6) AND Left([Name] , 4) <> "MSys" ORDER BY [Name]
asked Oct 23, 2014 at 18:52 user312854 user312854

2 Answers 2

You can run the SQL you have in a query, because that is what it is.

  1. Create a new query in query design, not with the wizard.
    enter image description here
  2. Close the table window.
    enter image description here
  3. Change the view to SQL and enter your string.
  4. Click RUN .
    enter image description here

answered Oct 23, 2014 at 19:19 22.9k 5 5 gold badges 59 59 silver badges 106 106 bronze badges

You can do it like this with DAO in VB :

Public Sub ListTablesDAO() Dim db As DAO.Database Dim tdf As DAO.TableDef Set db = CurrentDb For Each tdf In db.TableDefs If (tdf.Attributes And dbSystemObject) = 0 Then Debug.Print tdf.Name End If Next tdf db.Close: Set db = Nothing End Sub

and like this with ADO

Public Sub ListTablesADO() Dim rs As ADODB.Recordset Set rs = CurrentProject.Connection.OpenSchema(adSchemaTables) Do Until rs.EOF If rs!TABLE_TYPE = "TABLE" Then Debug.Print rs!TABLE_NAME End If rs.MoveNext Loop rs.Close: Set rs = Nothing End Sub

DAO and ADO provide two different object models for databases. Which version you choose depends on your preferences. You also need a reference to the respective libraries:

You can add these references in menu Tools > References :