How to Get Access VBA Query to List the Tables in a Database

Access stores information about the database structure in the MSysObjects table.

Microsoft Access is a database management system for individuals to create their own databases, reports and user forms. Access contains all the building blocks for developing small applications. You can create tables for data, stored procedures, user-defined functions and macros in addition to reports and forms. All these components have their own pointers or record definitions stored in a hidden system table. Access keeps track of every table, report, form, etc. you create and stores information such as when it was created or updated and system flags.

Instructions

Getting a List of Tables

1. Create a query to run the SQL command below. This query retrieves a list of all the tables in the current Access database. The where clause uses the number 1 to indicate that we only want the tables returned.

   SELECT MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Type, MSysObjects.Flags

   FROM MSysObjects

   WHERE MSysObjects.Type = 1

2. Return a list of only the non-system tables in the database with the example provided:

   SELECT MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Type, MSysObjects.Flags

   FROM MSysObjects

   WHERE MSysObjects.Type = 1 AND MSysObjects.Name Not Like "MSys*"

3. Return a list of only the non-hidden tables in the database by running the query below:

   SELECT MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Type, MSysObjects.Flags

   FROM MSysObjects

   WHERE MSysObjects.Type = 1 AND MSysObjects.Flags <> 8

4. Sort the list of non-hidden, non-system tables using the SQL code below:

   SELECT MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Type, MSysObjects.Flags

   FROM MSysObjects

   WHERE MSysObjects.Type = 1 AND MSysObjects.Flags <> 8 AND MSysObjects.Name Not Like "MSys*"

   ORDER BY MSysObjects.Name

Use the List of Tables in VBA

1. Load your list of tables into a record set object using the VBA code shown below:

   Dim rsMyTables As DAO.Recordset

   Set rsMyTables = CurrentDb.OpenRecordset("MyQuery")

2. Loop through each record returned in the query.

   Dim rsMyTables As DAO.Recordset

   Set rsMyTables = CurrentDb.OpenRecordset("MyQuery")

   Do While Not rsMyTables.EOF

   ' Code to manipulate data

   rsMyTables.MoveNext

   Loop

3. Add each table name to a drop-down list by modifying your while loop:

   Dim rsMyTables As DAO.Recordset

   Set rsMyTables = CurrentDb.OpenRecordset("MyQuery")

   Me.cmbMyComboBox.Clear

   Do While Not rsMyTables.EOF

   Me.cmbMyComboBox.AddItem rsMyTables![Name]

   rsMyTables.MoveNext

   Loop

4. Close your record set object so it releases the memory it is using:

   Dim rsMyTables As DAO.Recordset

   Set rsMyTables = CurrentDb.OpenRecordset("MyQuery")

   Me.cmbMyComboBox.Clear

   Do While Not rsMyTables.EOF

   Me.cmbMyComboBox.AddItem rsMyTables![Name]

   rsMyTables.MoveNext

   Loop

   rsMyTables.Close

   Set rsMyTables = Nothing

Tips & Warnings

• Macros are written in Visual Basic for Applications (VBA), which is the Microsoft Standard Office programming language.

• You can list out any of the objects in Access by changing the "MSysObject.Type" filter. The options for this value are listed below:

• Table= 1

• Query= 5

• Linked Table= 4, 6, or 8

• Form= -32768

• Report= -32764

• Module= -32761

References

• Perfect Paradigm: Using MSysObjects
• All FAQ Forums: Query to List All Tables, But Hidden
• Allen Browne: Microsoft Access Tips for Serious Users

Resources

• Everything Access: DDL Query Code Examples