MSysQueries中的数据是什么意思? [英] What does the data in MSysQueries mean?

问题描述

我一直在使用VBA来检查Access 2000数据库中的所有查询，表单和模块，但这可能非常繁琐且缓慢.最近，我决定仔细研究Access中的系统表，尤其是MSysQueries和MSysObjects.我可以使用这些表更快地检查数据库中的对象吗?当然，这些表是只读的，因此在不返回VBA的情况下，我无法通过它们对数据库进行任何修改. MSysQueries中的属性是什么意思?

解决方案

好吧，我遇到了

具有属性3的行表示存在UNION或DISTINCT关键字.

• 标志值0 =没什么特别的
• 标记值1 = UNION ALL
• 标记值2 = SELECT DISTINCT
• 标记值3 = UNION
• 标记值8 = SELECT DISTINCTROW
• 标记值9 =对主字段和子字段的查询

具有属性4的行指示查询是否来自外部数据库.如果属性4存在，则Name1将包含源.

具有属性5(可以有多个)的行指示在查询中找到的每个表.如果查询是UNION查询，则Expression字段在UNION关键字上进行拆分，并且Name2字段具有系统生成的表别名.对于查询中的所有其他表，Name1是表的名称，Name2是别名(如果有的话).

具有属性6(可以有多个)的行指示查询中的每个单个字段或表达式.如果查询没有属性6，则假定的行为是包括所有字段. Expression字段包含每个字段表达式或名称，Name1包含字段别名(如果存在).

• 标记值0 =字段或表达式的值
• 标记值1 =该字段是交叉表查询中的列标题.
• 标记值2 =该字段是交叉表查询中的行标题.

具有属性7(可以有多个)的行指示每个单个联接"ON"表达式.表达式字段包含实际的联接表达式. Name1包含联接中的第一个表. Name2包含联接中的第二个表.

• 标志值1 =内部联接
• 标志值2 =左联接
• 标记值3 =右加入

具有属性8的行在表达式"字段中包含整个WHERE子句.如果没有where子句，则查询中省略属性8.

具有属性9(可能有多个)的行指示查询的GROUP BY子句中的每个单个Group By表达式. 表达式"字段按表达式包含每个组.

• 标记值0 =字段或表达式的值
• 标记值1 =该字段是交叉表查询中的列标题.
• 标记值2 =该字段是交叉表查询中的行标题.

具有属性11(可以有多个)的行指示查询的ORDER BY子句中的每个单个Order By表达式.表达式字段按表达式包含每个顺序. Name1带有"D"或"d"以表示排序是按降序进行的.

属性255的行是查询的结尾.

我不完全确定Order字段的作用，但我确实发现它不是Null，尽管有时它的值为空字符串，但它并不总是具有该值.空字符串出现在属性5、6、7和9上，但这些属性并不总是空字符串.

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.

• Flag value 1 = SELECT query.
• Flag value 2 = SELECT ... INTO query, or a make table query. Name1 will have the name of the table that is created.
• Flag value 3 = INSERT query; Name1 will have the name of the table to insert to.
• Flag value 4 = UPDATE query
• Flag value 5 = DELETE query
• Flag value 6 = Crosstab query (TRANSFORM)
• Flag value 9 = UNION 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.

• 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 on master fields and child fields

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.

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

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.

• Flag value 1 = Inner Join
• Flag value 2 = Left Join
• Flag value 3 = Right 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.

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

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.

这篇关于MSysQueries中的数据是什么意思?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！