I've been using VBA to examine all the queries, forms, and modules in my Access 2000 database, but it can be quite tedious and slow. Recently, I decided to take a closer look at the system tables in Access, in particular, MSysQueries and MSysObjects. Can I use these tables to examine my objects in the database faster? Of course, these tables are read-only, so I can't make any modifications to the database through them without returning to VBA. What do the attributes in MSysQueries mean?
Well, I came across this post on Google groups. I did further investigation on my own tables and wanted to share a table of information that I created inspired by work already done.
Each query can take up multiple rows in the table.
The row with attribute 0 is the beginning of the query.
The row with attribute 1 indicates the type of the query.
The rows with attribute 2 (there could be multiple) are each formal parameter of the query. The Flag column indicates the data type (i.e. "10" for dbText) and the Name1 column indicates the name of the parameter. If there are no rows with attribute 2, then the query does not have formal parameters.
The row with attribute 3 indicates the presence of UNION or DISTINCT keywords.
The row with attribute 4 indicates if the query comes from an external database. Name1 will contain the source if attribute 4 exists.
The rows with attribute 5 (there could be multiple) indicate each table found in the query. If the query is a UNION query, the Expression field has a split on the UNION keyword and the Name2 field has a system-generated table alias. For all other tables in a query, Name1 is the name of the table and Name2 is the alias, if there is one.
The rows with attribute 6 (there could be multiple) indicate each single field or expression in the query. If there is no attribute 6 for the query, the behavior assumed is that all fields are included. The Expression field contains each field expression or name, and Name1 contains the field alias if there is one.
The rows with attribute 7 (there could be multiple) indicate each single join "ON" expression. The Expression field contains the actual join expression. Name1 contains the first table in the join. Name2 contains the second table in the join.
The row with attribute 8 contains the whole WHERE clause in the Expression field. If there is no where clause, attribute 8 is omitted from the query.
The rows with attribute 9 (there could be multiple) indicate each single Group By expression in the GROUP BY clause of the query. The Expression field contains each group by expression.
The rows with attribute 11 (there could be multiple) indicate each single Order By expression in the ORDER BY clause of the query. The Expression field contains each order by expression. Name1 has "D" or "d" to indicate that the sort is done in descending order.
The row with attribute 255 is the end of the query.
I'm not exactly sure what the Order field does, but I did find that it is not Null, and though it sometimes has a value of an empty string, it doesn't always have that value. Empty strings occur on attributes 5, 6, 7, and 9, but it is not always an empty string for those attributes.
Thanks to @Bobort great explanations, I was able to create a query that lists all queries in current database, with their input tables/queries, query type, and target table (for action queries).
I thought I could share that here.
SELECT MSysObjects.Name AS queryName,
Mid("SelectMakTblAppendUpdateDeleteXtab 777777PassThUnion ",([msysqueries]![Flag]-1)*6+1,6) AS queryType,
src.Name1 AS [Input],
MSysQueries.Name1 AS Target
FROM (MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id)
LEFT JOIN (select * from MSysQueries WHERE Attribute = 5 ) AS src
ON MSysQueries.ObjectId = src.ObjectId
WHERE (((MSysObjects.Name)>"~z") AND ((MSysQueries.Attribute) =1))
ORDER BY MSysObjects.Name, src.Name1;
To use, just create a query in SQL view and paste the above code.
Further to Bobort and iDevlop's answers:
The row with attribute 1 indicates the type of the query.
CREATE TABLE...
)The row with attribute 3 indicates the predicate.
The rows with attribute 5 (there could be multiple) indicate each FROM table/query found in the query
The row with attribute 10 contains the whole HAVING clause in the Expression field. If there is no HAVING clause, attribute 10 is omitted from the query.
The Order
field is a BIG-ENDIAN binary value that contains an array of 4 bytes (binary fields can be added with VBA, but cannot be added using the UI, unless you copy and paste from a binary field in a system table.) However, in most databases, in the MSysQueries
table, you're unlikely to encounter binary values greater than 255, so you can shortcut the conversion to a byte by inspecting the byte at index 3. For example:
Sub EnumOrder()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset( _
" SELECT * FROM MSysQueries " & _
" WHERE Attribute = 6 " & _
"ORDER BY ObjectId Asc, [Order] Asc")
With rst
Do While Not .EOF
Debug.Print .Fields("ObjectId"), .Fields("Order")(3)
.MoveNext
Loop
.Close
End With
End Sub
Cumulative values occur for Attribute 3. So additional items include:
I have written an extended article about the workings of the MSysQueries table. See How Access stores queries.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With