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

Written by susan hare
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Get Access VBA Query to List the Tables in a Database
Access stores information about the database structure in the MSysObjects table. (data storage image by Kir from Fotolia.com)

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.

Skill level:
Easy

Other People Are Reading

Instructions

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

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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.