Access: list query dependencies

Question

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

EDIT: 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.

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

`Attribute = 5` seems to get all my queries, which is what I used — ashareef, Aug 08 '14 at 15:48
I also found, for those who can't -or do not want- to use the built-in dependency checker, an excellent free tool, which does not job when the native one crashes (and ignores all your VBA anyway): http://www.accessdependencychecker.com/ — iDevlop, Jul 17 '15 at 15:53

iDevlop · Accepted Answer · 2014-09-09T13:27:54.603

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*"));

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

score 4 · Answer 2 · answered Aug 08 '14 at 15:23

4

Access does have a built in “dependency” feature. The result is a VERY nice tree-view of those dependencies, and you can even launch such objects using that treeview of your application to “navigate” the application so to speak.

The option is found under database tools and is appropriately called Object Dependencies.

The result looks like this: enter image description here

While you don't want to use auto correct, this feature will force on track changes. If this is a large application, then on first run a significant delay will occur. After that, the results can be viewed instantly.

answered Aug 08 '14 at 15:23

Albert D. Kallal

42,205
3
34
51

Thanks Albert. I thought I read somewhere to avoid using this. But perhaps I am mistaking with Autocorrect feature. – iDevlop Aug 08 '14 at 15:45
And anyway, I really like my little query there. If you paste its results to Excel and use Autofilter, you can really narrow the scope of the "report" to the queries in scope for an issue. Then I can print that for meetings or to avoid wasting my screen space. Try it! ;-) – iDevlop Aug 08 '14 at 15:49
1

I noticed that this feature does not work properly when you use table alias. The ALIASes are reported as MISSING. Fortunately, the underlying tables appear anyway, but that's a bit confusing. – iDevlop Aug 11 '14 at 07:46
1

The feature to avoid is “perform” track name autocorrect. However you can safely turn on Track name autocorrect info. And you are correct that this dependency display is not perfect, but it certainly a useful and little known feature, and one that allows you to “click” on any object in the treeview to launch that object in design mode. – Albert D. Kallal Aug 11 '14 at 22:16

ashareef · Answer 3 · 2014-08-08T15:07:06.690

1

Would this query meet your needs? Shows what objects are used to create each query.

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));

Found this as well if you're interested in querying for other objects

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

edited Aug 08 '14 at 15:07

answered Aug 08 '14 at 14:56

ashareef

1,846
13
19

Definitely no. My query returns the source(s) and eventual target, thus creating a kind of crossref. It is rather complex but very useful when you receive a db with a few hundred queries calling each other. – iDevlop Aug 08 '14 at 15:08
I realized you had the sources showing, I've modified the query to have sources now, I'll attempt to read the original query again and see why it only shows particular queries. – ashareef Aug 08 '14 at 15:12
Good point, I am rebuilding based on that. However, that does not show Passthru queries. – iDevlop Aug 08 '14 at 16:12

Access: list query dependencies

3 Answers3

Linked