What does the data mean in MSysQueries?

Question

I use VBA to examine all the queries, forms, and modules in an Access 2000 database, but this 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 more quickly examine my objects in the database? Of course, these tables are read-only, so I cannot make any changes to the database through them without returning to VBA. What do attributes mean in MSysQueries?

Answer 1

Well, I came across this post in google groups. I did further research on my own desks and wanted to share a table of information that I created, inspired by the work already done.

Each query can occupy several rows in the table.

A string with attribute 0 is the beginning of the request.

A line with attribute 1 indicates the type of request.

• Flag value 1 = SELECT query.
• Flag value 2 = query SELECT ... INTO or query table make. Name1 will have the name of the created table.
• Flag value 3 = INSERT request; Name1 will have the name of the table to insert.
• Flag value 4 = UPDATE query
• Flag value 5 = DELETE request
• Flag value 6 = crosstab query (TRANSFORM)
• Flag value 9 = UNION request

Lines with attribute 2 (there may be several) are formal request parameters. The Flag column indicates the data type (ie, "10" for dbText), and the Name1 column indicates the parameter name. If there are no rows with attribute 2, the request has no formal parameters.

A line with attribute 3 indicates the presence of the keywords UNION or DISTINCT.

• Flag value 0 = nothing special
• Flag value 1 = UNION ALL
• Flag value 2 = SELECT DISTINCT
• Flag value 3 = UNION
• Flag value 8 = SELECT DISTINCTROW
• Flag value 9 = Queries by main fields and child fields

A row with attribute 4 indicates whether the query is coming from an external database. Name1 will contain the source if attribute 4 exists.

Rows with attribute 5 (maybe several) indicate each table found in the query. If the query is a UNION query, the Expression field is partitioned by the UNION keyword, and the Name2 field has a table alias generated by the system. For all other tables in the query, Name1 is the name of the table, and Name2 is the alias, if any.

Lines with attribute 6 (maybe several) indicate each individual field or expression in the query. If the request does not have attribute 6, it is assumed that all fields are included. The Expression field contains each field expression or name, and Name1 contains the alias of the field, if any.

• Flag value 0 = Field or expression value
• Flag value 1 = The field is the column heading in the crosstab query.
• Flag value 2 = Field is the row header in the crosstab query.

Lines with attribute 7 (maybe several) indicate each individual expression "ON". The Expression field contains the actual union expression. Name1 contains the first table in the join. Name2 contains the second table in the join.

• Flag Value 1 = Internal Connection
• Flag value 2 = left join
• Flag Value 3 = Right Connection

A line with attribute 8 contains the entire WHERE clause in the Expression field. If the where clause is missing, attribute 8 is not specified in the request.

Lines with attribute 9 (there may be several) indicate each individual Group By expression in the GROUP BY clause of the query. The Expression field contains each group by expression.

• Flag value 0 = Field or expression value
• Flag value 1 = The field is the column heading in the crosstab query.
• Flag value 2 = Field is the row header in the crosstab query.

Lines with attribute 11 (there may be several) indicate each Order By expression in the ORDER BY clause of the query. The Expression field contains each order by expression. Name1 has a "D" or "d" to indicate that sorting is performed in descending order.

The line with attribute 255 is the end of the request.

I'm not quite sure what the Order field does, but I found that it is not Null, and although sometimes it has an empty string value, it does not always have this value. Blank lines are found in attributes 5, 6, 7, and 9, but this is not always an empty string for these attributes.

Answer 2

Thanks to @Bobort's great explanations, I was able to create a query that lists all the queries in the current database, with their input tables / queries, query type and target table (for action queries).
I thought I could share this.

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 the SQL view and paste the above code.

Answer 3

In addition to Bobort and iDevlop answers:

A line with attribute 1 indicates the type of request.

• Flag value 7 = DDL query (e.g. CREATE TABLE... )
• Flag Value 9 = Pass Through Request

A line with attribute 3 indicates the predicate.

• Flag value 1 = All values ​​or UNION ALL (if query is UNION)
• Flag Value 4 = WITH PROPERTY OPERATOR
• Flag value 16 = TOP N
• Flag Value 48 = TOP N PERCENT

Rows with attribute 5 (maybe several) indicate each table / FROM query found in the query

• The expression contains a FROM source or SELECT statement if the query is UNION

The line with attribute 10 contains the whole HAVING clause in the Expression field. If there is no HAVING clause, attribute 10 is not specified in the request.

The Order field is a BIG-ENDIAN binary value that contains an array of 4 bytes (binary fields can be added using VBA, but cannot be added using the user interface unless you copy and paste from the binary field into the system table. ) However, in most databases in the MSysQueries table MSysQueries you are unlikely to encounter binary values ​​greater than 255, so you can reduce the conversion to bytes by checking the bytes in 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 

Answer 4

Aggregate values ​​are found for attribute 3. Thus, additional elements include:

• Flag 12 CHOOSE DISTINCT .... WITH AN OPTION OF ALL ACCESS
• Flag 18 CHOOSE VARIOUS TOP (i.e. 2 + 16)
• Flag 24 CHOOSE VARIOUS TOP (i.e. 8 + 16)
• Flag 50 CHOOSE VARIOUS TOP PERCENT (i.e. 2 + 48)
• Flag 56 CHOOSE VARIOUS TOP PERCENT (i.e. 8 + 48)