I want to compile a list of metadata about queries within an MS Access DB. For each query I want a listing of the query’s fields. The database I’m working in has three kinds of queries: Append, Select, Union.
I have done this with code like…
for each qdef in db.qdefs
for each field in qdef.fields
(write query name and field name to a table)
next
next
This works just fine for select queries and union queries, but where are my Append Queries?
So I need a way to compile metadata about a collection of Append Queries with a row for each field containing the query name, a source field name, and a destination field name. My assumption is that Append queries must be part of a different collection than select queries, but so far I haven’t been able to find it. I’ve searched online but so far haven’t found a set of key words that doesn’t return an ocean of unrelated info about stuff like “What is an append query” or “How to run an append query with vba”
I tried looking at the qdefs collection to find metadata about Append Queries in MS Access. No joy.
1
Saved Append queries are included in the QueryDefs collection. Here is a simple query named qryAddToOne:
INSERT INTO tblOne ( some_date )
VALUES (Date());
Then in the Immediate window, I can reference it in the collection by its name:
? CurrentDb.QueryDefs("qryAddToOne").Fields.Count
0
The reason you didn’t see append queries is that you only output the query name from within a For loop based on the QueryDef.Fields. But it seems Access does not populate a Fields collection for append queries — leaving Fields.Count at zero.
Not sure whether this will help, but if you want to determine whether a QueryDef is an append query, you can check its Type property:
? CurrentDb.QueryDefs("qryAddToOne").Type = dbQAppend
True
Well, here’s a workaround by using the hidden system table MSysQueries.
Warning upfront: Take a look and do not change anything! You are working in a system table and you can easily corrupt your database.
Unhide:
- Make sure your navigation-pane lists also the hidden (system) tables.
- Select
MSysQueriesand take a look.
Alternative: Create a select query like SELECT * FROM MSysQueries.
More info here : What does the data in MSysQueries mean?
Thanks for the great tips! I was hoping for a neat way in VBA to output append query metadata, and was confused why they didn’t show up with the rest of the queries, but your comment makes sense that the fields collection isn’t populated for append queries–which are more complicated in a way than other queries.
I did end up exporting msysqueries to Excel, and using that link about what MSysQueries data means I was able to filter out everything else leaving just the append queries. What was left gave me the information I needed and with a bit of color coding it will be handy for a quick reference on this project I’m working.
Thanks again for the help!