Data Management

How to create a list of tables or queries from Access

Need to create a list of tables or queries within Microsoft Access? Check out the two methods provided here by Todd Parker. They're quick, painless, and easy to use.

By Todd Parker

Have you ever needed to display a list of tables or queries in Access to your users? Perhaps you found that it would come in handy during development? It’s actually a simple thing to do.

Once you gather this information, you can populate a ComboBox and allow your users to select the query they would like to view, and at the same time, protect your Database Window. I actually use this information to help me keep track of my queries. I populate a table with all of my query names and then track additional information on those queries, above and beyond the information that Access allows you to hold in the Description column.

There are two ways to create this list. It depends on what you plan to do with the data once you get it and on your personal preference. If you are going to populate a table with this information, a query can make that a simple trick. If you are going to use this information in code, then you might as well use the database object and access its TableDef and QueryDef collections. Because we are not all programmers, I will show you both ways of creating the list.

IMPORTANT NOTE
Before you continue, please understand that if you use the query method, you will be accessing some of Microsoft’s hidden system tables. These tables should not be edited by anyone, unless you really know what you are doing. It’s actually quite difficult to modify this data due to built-in bindings. But just to be safe, set these queries to SnapShot recordsets, or in code use the acReadOnly argument. I will point out these properties to you in each of the methods described here.

The query method
First, so that you can gain an understanding of where you will get this data, make the Hidden System Tables visible. Use the Tools | Options menu and select the View tab.

Place a check mark in the System Objects box. Apply the changes and click OK. You will now be able to see some MSys* tables in your Tables Window. The icons for these objects should be slightly grayed out.

After you are done viewing these tables, it is best to hide them again. This makes them less likely to be noticed by the next person fooling around in the Tables Window. Your queries will still work, even if these tables are not visible.

Now create a new query in design view. Add the table called [MSysObjects] and select the fields called [Name] and [Type]. The criteria of [Type] = 5 will get your query names, and a criteria of [Type] = 1 with a [Flags] = 0 will get your table names. However, keep in mind that tables of different types will require that you modify your query accordingly. For instance, Linked Tables have a [Type] = 6. In such cases, you could add [Type] = 6 as yet another criteria for [Type]. Now, using the Query Properties, set the RecordsetType to SnapShot.

The code method
Below, you will find a method using code to create the list of tables and queries using the Database Object and its QueryDef and TableDef collections. This method is the recommended way for accomplishing this task and is actually much more versatile. The QueryDefs collection contains the name of each query in your database, and the names for your tables in the TablesDefs collection. When scanning for tables, you obviously do not want to include system tables, so make sure you include an If statement that looks for tables with an attribute of 0.
Public Sub GetMyObjectsList()
Dim db As Database
Dim Qry As QueryDef
Dim QryNames As String
Dim QryCount As Integer
Dim Tbl As TableDef
Dim TblNames As String
Dim TblCount As Integer

Set db = CurrentDb
QryCount = 0
TblCount = 0

For Each Qry In db.QueryDefs
 QryNames = QryNames & Qry.Name & ", "
 QryCount = QryCount + 1
Next

For Each Tbl In db.TableDefs
 If Tbl.Attributes = 0 Then 'Ignores System Tables
 TblNames = TblNames & Tbl.Name & ", "
 TblCount = TblCount + 1
 End If
Next

QryNames = QryNames & "TOTAL Query Count: " & QryCount
TblNames = TblNames & "TOTAL Table Count: " & TblCount
MsgBox QryNames
MsgBox TblNames

db.Close
Set db = Nothing

End Sub
Public Sub SetQryString()

For those code freaks out there, here's some bonus code.
The following is the Query method, in code. This comes in handy when you need to programmatically manipulate your Query. Create two Blank Queries and call them "FindQueriesQry" and "FindTablesQry." Notice the OpenQuery method is utilizing the acReadOnly argument.
Dim db As Database
Dim MySQLString As String
Dim QryDef As QueryDef
Dim TypeOfObject As Integer

Set db = CurrentDb
TypeOfObject = 5 'To Find Queries
MySQLString = "Select Name, Type from MSysObjects where Type = " & TypeOfObject

Set QryDef = db.QueryDefs("FindQueriesQry")
QryDef.SQL = MySQLString
DoCmd.OpenQuery "FindQueriesQry", acViewNormal, acReadOnly

TypeOfObject = 1 'To Find Tables
MySQLString = "Select Name, Type from MSysObjects where Type = " & TypeOfObject & _
 " and Flags = 0"

Set QryDef = db.QueryDefs("FindTablesQry")
QryDef.SQL = MySQLString
DoCmd.OpenQuery "FindTablesQry", acViewNormal, acReadOnly

db.Close
Set db = Nothing

End Sub

One more reason that the code method is best
I suppose the choice of methods raises an obvious question—which is faster? Truthfully, both are extremely fast. So speed is not an issue here. However, I can tell you that if you decide you would also like to see the Field Names contained in the queries and tables, you are going to want to use the code method.

While the MSysObjects table houses this information for queries only, it is quite cumbersome to sift through. You have to join the MSysObjects table to the MsysQueries table on the [Id] to [ObjectId], and then request both the [Expression] and [Name1]. The code method, on the other hand, already contains a collection called Fields for both tables and queries.

Todd Parker is a developer for Science Applications International Corporation in New Orleans. SAIC deals mostly with nuclear and other energy related companies. Todd has been in the IT field for over 8 years, the majority of that time he has worked as an IT consultant.

2 comments
BtrWrld
BtrWrld

I believe I am doing something wrong since I can not make this work. Can you dummy down the instructions for someone with very limited understanding of VBA in Access and Access itself. I have the Query Method working but I get errors from the Code Method. I always get a compile error. Also, is that last Public Sub supposed to be in the code?

robert.d
robert.d

Following the instructions in the thread "How to create a list of tables or queries from Access" I did create a query that lists all the tables and queries in my data base. I also located a complete list of the flag definitions: Type Flag ID Append Query 64 Append Query (Hidden) 72 Crosstab Query 16 Crosstab Query (Hidden) 24 Data-Definition 96 Delete Query 32 Delete Query (Hidden) 40 Deleted or temp ( prefixed with ~) 3 Hidden 8 Linked Table 2097152 Make-Table 80 Make-Table (Hidden) 88 MSys Table (w/ read permission) -2147483648 MSys Table (w/o read permission) 2 No Code In Table Select Query or standard table 0 Union Query 128 Union Query (Hidden) 136 Update Query 48 Update Query (Hidden) 56 Now I would like to add the database file name(and path if possible) to this query so I can copy and paste into my reports to allow other users to locate the data sources.

Editor's Picks