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...

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

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

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.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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy