访问:列出查询依赖项 [英] Access: list query dependencies

问题描述

在此答案的帮助下，我创建了一个报告以列出当前数据库中的所有查询及其输入表/查询及其输出(用于Actions查询).
我对此感到非常满意，直到发现结果中缺少一些查询.
我对原因有些困惑.
有什么线索吗?

With the help of this answer, I created a report to list all queries in the current db, with their input tables/queries, and their output (for Actions queries).
I have been very happy with it, until I noticed that some queries are missing in the result.
I am a bit stuck on why.
Any clue ?

SELECT MSysObjects.Name AS queryName, 
  Mid("SelectMakTblAppendUpdateDeleteXtab  777777PassThUnion ",([msysqueries]![Flag]-1)*6+1,6) AS queryType, 
  src.Name1 AS [Input], 
  MSysQueries.Name1 AS Target, 
  MSysQueries.Attribute
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

发现在所有逻辑下，INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id有时并不返回它应该的每一行.
我同时检查了MSysQueries和MSysObjects，并确保两边都有相同的对象ID -2147483618，并确保MSysQueries的ObjectId所在的行具有Attribute = 1，但是，在连接表时，该行具有attribute = 1的特定行没有出现.很奇怪.我尝试使用内部联接，用条件替换联接，在过程中添加Val或CLng，没有办法.我在这里迷路了.

found that against all logic, INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id sometimes does not return every line it should.
I checked both MSysQueries and MSysObjects and made sure I had same object id -2147483618 on both sides, I made sure that MSysQueries has a line for that ObjectId where Attribute=1, however, when joining the tables, that specific line with attribute=1 does NOT appear. Very strange. I tried to use an inner join, to replace the JOIN by a criteria, adding Val or CLng in the process, no way. I am lost here.

找到了一种使用where CStr([Id]) = CStr([ObjectId])正确联接"两个表的方法.
但这确实没有必要！

EDIT 2: found a way to correctly "join" both tables by using where CStr([Id]) = CStr([ObjectId]).
But that should really not be necessary !

推荐答案

对于感兴趣的人，我现在终于可以了:

I finally got it ok now, for those who are interested:

SELECT MSysObjects.Name AS QueryName, Nz([expression],[name1]) AS Source, 
     MSysQueries.Name2 AS Alias, MSysObjects.Flags, t.Target
FROM (MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId) 
LEFT JOIN (SELECT ObjectId, Name1 as Target FROM MSysQueries 
            WHERE (Name1 Not Like "ODBC*") AND (Attribute=1))  AS t 
ON MSysObjects.Id = t.ObjectId
WHERE ((MSysQueries.Attribute=5)) OR ((MSysQueries.Name1 Like "ODBC*"));  

对于直通查询，我选择显示完整的SQL语句，当然可以对其进行调整.我将其用作漂亮报告的源，或者将数据复制到Excel工作表，然后使用自动筛选器缩小列表范围，直到它澄清目标表的内容来自何处.

For the passthru queries I chose to display the full SQL statement, that can be tweaked of course. I use it as the source of a nice report, or I copy the data to an Excel sheet and use the Autofilter to narrow the list until it clarifies where the contents of a target table come from.

在报告中，我使用以下功能来显示查询类型:

In the report I use the following function to display the query type:

Function GetQueryType(Flags) as String
        Select Case (Flags And 247)      'Bit And 247: to clear the Hidden flag=8
            Case 0:    GetQueryType = "SELECT "
            Case 16:   GetQueryType = "XTAB "
            Case 32:   GetQueryType = "DELETE "
            Case 48:   GetQueryType = "UPDATE "
            Case 64:   GetQueryType = "APPEND "
            Case 80:   GetQueryType = "MAKE TABLE "
            Case 112:  GetQueryType = "PASS THRU"
            Case 128:  GetQueryType = "UNION"
            Case 3:    GetQueryType = "Report"
            Case Else: GetQueryType = "Other: " & (Flags And 247)
        End Select
End Function

这篇关于访问:列出查询依赖项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！