访问:列出查询依赖项 [英] 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屋!