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]

2 Answers

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.
   
2. Close the table window.
   
3. Change the view to SQL and enter your string.
4. Click RUN.
   

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:

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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