Access: list query dependency

Question

With this answer, I created a report to list all the queries in the current db with their input tables / queries and their result (for Actions queries).
I was very pleased with this until I noticed that as a result, some requests were missing.
I am a little fixated 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 

EDIT: Found that against all INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id logic, INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id sometimes does not return every row that should be.
I checked both MSysQueries and MSysObjects and made sure that I have the same object identifier -2147483618 on both sides, I made sure that MSysQueries has a row for ObjectId, where Attribute = 1, however when attaching to tables this particular row with attribute = 1 does not appear. Very strange. I tried using the inner join to replace the JOIN with the criteria by adding Val or CLng to the process, in no way. I'm lost here.

EDIT 2: Found a way to correctly "join" both tables with where CStr([Id]) = CStr([ObjectId]) .
But it really is not necessary!

Answer 1

I finally got approval for those 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*")); 

For passthru queries, I decided to display the full SQL query, which can be modified, of course. I use it as a source of a good report or copy data on an Excel worksheet and use Autofilter to narrow the list until it finds out where the contents of the target table came from.

In the report, I use the following function to display the type of request:

 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 

Answer 2

Access has a built-in dependency function. The result is a VERY nice tree view of these dependencies, and you can even run such objects using this tree view of the application to "navigate" the application, so to speak.

This option is located in the database tools and is accordingly called object dependencies.

The result is as follows:

While you do not want to use automatic correction, this function will affect track changes. If this is a large application, there will be a significant delay on the first launch. After that, the results can be viewed instantly.

Answer 3

Will this request meet your needs? Shows which objects are used to create each request.

 SELECT MSysObjects.Name, MSysQueries.Name1 FROM MSysObjects LEFT JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId WHERE ((MSysObjects.Name Not ALike "~%") AND (MSysQueries.Attribute=5) AND (MSysObjects.Type=5)); 

This is detected if you are interested in requesting other objects.

http://access.mvps.org/access/queries/qry0002.htm