Object Dependency

[RobertG]

Hi All,

I have a multi-part issue and am not sure this is the correct forum, so feel
free to move this post, as necessary. With that being said, allow me to
briefly (will go into more detail, if needed) describe my problem(s):

Recently, I was given a database to dissect and try to make sense of.
Currently, it is just a bunch of tables and queries... nothing more. One of
the things the users of this database have asked is for the ability to see
object dependencies of their queries. Since this is a Access 2003 database,
my first suggestion was to use the built-in feature to display this
information... no go. They wanted something easier. They wanted to be able
to select the name of an object from a combo box, click a command button,
have all dependencies displayed, and show whether the dependency is a table
or query. After doing a little bit of research, here, as well as other
sites, I came up with something that semi-works, and now I need a little bit
of help.

Ideally, I will have a form with a combo box for them to select the object,
a button to run this code, and a text box (?) to display object dependency
info. I'm not sure if this is the best way to do this, but it's what I was
able to come up with.

Option Compare Database
Option Explicit

Sub DependencyTest()

Dim depInf0 As DependencyInfo, depInf1 As DependencyInfo, depInf2 As
DependencyInfo, _
depInf3 As DependencyInfo, depInf4 As DependencyInfo
Dim obj0 As AccessObject, obj1 As AccessObject, obj2 As AccessObject,
obj3 As AccessObject, _
obj4 As AccessObject

Debug.Print [Forms]![frmObjectNames].[Form]![cboObjectNames]
Set depInf0 =
Application.CurrentData.AllQueries([Forms]![frmObjectNames].[Form]![cboObjectNames]).GetDependencyInfo
For Each obj0 In depInf0.Dependencies
If obj0.Type = acTable Then
Debug.Print " Sub 1: " & obj0.Name & " (TABLE)"
ElseIf obj0.Type = acQuery Then
Debug.Print " Sub 1: " & obj0.Name & " (QUERY)"
Else
Debug.Print " Sub 1: " & obj0.Name & " (UNION QUERY)"
End If
Set depInf1 = obj0.GetDependencyInfo
For Each obj1 In depInf1.Dependencies
If obj1.Type = acTable Then
Debug.Print " Sub 2: " & obj1.Name & " (TABLE)"
ElseIf obj1.Type = acQuery Then
Debug.Print " Sub 2: " & obj1.Name & " (QUERY)"
Else
Debug.Print " Sub 2: " & obj2.Name & " (UNION QUERY)"
End If
Set depInf2 = obj1.GetDependencyInfo
For Each obj2 In depInf2.Dependencies
If obj2.Type = acTable Then
Debug.Print " Sub 3: " & obj2.Name & " (TABLE)"
ElseIf obj2.Type = acQuery Then
Debug.Print " Sub 3: " & obj2.Name & " (QUERY)"
Else
Debug.Print " Sub 3: " & obj2.Name & " (UNION
QUERY)"
End If
Set depInf3 = obj2.GetDependencyInfo
For Each obj3 In depInf3.Dependencies
If obj3.Type = acTable Then
Debug.Print " Sub 4: " & obj3.Name & "
(TABLE)"
ElseIf obj3.Type = acQuery Then
Debug.Print " Sub 4: " & obj3.Name & "
(QUERY)"
Else
Debug.Print " Sub 4: " & obj3.Name & "
(UNION QUERY)"
End If
Set depInf4 = obj3.GetDependencyInfo
For Each obj4 In depInf4.Dependencies
If obj4.Type = acTable Then
Debug.Print " Sub 5: " &
obj4.Name & " (TABLE)"
ElseIf obj4.Type = acQuery Then
Debug.Print " Sub 5: " &
obj4.Name & " (QUERY)"
Else
Debug.Print " Sub 5: " &
obj4.Name & " (UNION QUERY)"
End If
Next
Next
Next
Next
Next
End Sub

Basically, this code looks at the selection the user has made from
cboObjectNames (since this database only has tables and queries, I only need
to look at the queries to see what they are dependant upon) to see what they
are dependant upon. If the query is based on another query, it will display
what that query is dependant upon, too. Currently, it will do this up to 5
levels deep.

So I'm sure you're dying to know what I need help on, right? Ok!

1. As you will notice, I have "Debug.Print" sprinkled in that code all over
the place, so my results are only displayed in the immediate window.
Obviously, this is no good for the end user, but, unfortunately, I have NO
idea how to pull this information and display it on the form the user will,
err, use. This is my biggest issue... if I can't get this to work, there is
no point going to my next problem.

2. As I mentioned earlier, this code is only set to display dependancies up
to 5 levels deep. Right now, that is fine as there are no queries that have
that many nested one's, but you never know in the future... I would like to
make this code more scalable, if possible.

3. All the queries are based off tables or other queries. Unfortunately,
several of these queries are union queries and those are not displayed at all
when this code is run. Ideally, those would be displayed as well.

4. I want to be able to display what the object is. As you can see, if the
object type is = acTable, (TABLE) is displayed... acQuery, (QUERY) is
displayed. I have coded a line for (UNION QUERY), but since those don't
display at all right now, just disregard those lines of code.

Here is a sample of what is displayed in the immediate window when this code
is run:

DependencyTest
EXTRACT - LNI PUB - ANES - FOR CSV FILE
Sub 1: ~Anesthesia FS (TABLE)
Sub 1: LNI Conversion Factors (TABLE)
Sub 1: Fees - Publication, System, Appeal - LNI (QUERY)
Sub 2: COVERAGE LNI - Step 2 (QUERY)
Sub 3: ~Code Groupings (TABLE)
Sub 3: COVERAGE LNI - Step 1 (QUERY)
Sub 4: ~DATE (TABLE)
Sub 4: LNI Coverage (TABLE)
Sub 2: Possible PMDs, RVUs and Fees (QUERY)
Sub 3: ~Code Groupings (TABLE)
Sub 3: Possible Fees - Step 1-4 (QUERY)
Sub 3: Possible PMDs and Fees - ALL (QUERY)
Sub 4: Possible Fees - Step 7-1 (QUERY)
Sub 1: Valid Codes - Step 3-3 - LNI (QUERY)

I know that was a lot of information, but I probably didn't give enough. If
anyone can help me with this problem, I would be very appreciative. If you
DO need more info, don't hesitate to ask... I'll supply as much as I can.

Thanks!

Robert

 

[Allen Browne]

Big question!

In answer to your specifics below, but just a warning that my experience
with the DependecyInfo in Access 2003 and later is not good. Firstly, it
requires Name AutoCorrupt to be turned on, and I don't trust what Access
says when this is so. It confuses fields with names that used to exist. Even
captions and aliases get confused with field names. Here's an old list of
some of the other problems it causes:
http://allenbrowne.com/bug-03.html
There's more, but that should be enough to show why it's not a good idea.

If that's not bad enough, it's very easy to crash Access by reading the
object dependency info. For example, it's quite common to have a left-over
query that refers to a table that no longer exists in the database. So for a
database that's been around for a while, you may find you cannot even run
the object depency info without Windows shutting crashing Access down. After
hours of frustration, I've given up on it competely.

Anyway, some info regarding your specific questions:

Q1: Showing results intead of Debug.Print
==============================
Instead of the Debug.Print statements, create a table where the comments
will be stored. At the top of your code, clear out any existing comments:
db.Execute "DELETE FROM MyCommentTable;", dbFailOnError
Then insert the comments into the table instead of the debug.prints. Either
execute an INSERT statement for each one, or OpenRecordset() and AddNew with
Update.

Once the comments are in the table, you can use all the functionality of
reports to display them to the user.

Q2: Going more than 5 levels deep
=========================
I haven't gone through your code in detail, but you could probably redesign
it to be recursive. If you've never done this before, it's not conceptually
difficult. For example, code that lists the files in a folder can call
itself again for each of the folders it finds so it lists the files in the
subfolders also, and so on down the tree.

Here's an example of recursive code to list files:
http://allenbrowne.com/ser-59.html
and here's an example that examines a form and calls itself recursively to
handle subforms:
http://allenbrowne.com/ser-56.html

The biggest issue here is infinite recursion. Haven't tested, but it may be
possible to create Query1 that uses Query2. Make Query2 use Query3, and then
modify Query1 so it uses Query3 too. Access supports nesting queries to 50
levels, so this could get interesting.

Q3: Union queries not displayed
=======================
I'm not sure why the UNION queries are not displayed. Perhaps you could get
at them via the QueryDefs collection, or via CurrentData.AllQueries.


Here's a utility by Chas Dillon for renaming fields and tracing
dependencies:
http://allenbrowne.com/ser-41.html
and here's one that identifies where are field name is used in an
application:
http://allenbrowne.com/ser-73.html

Hope that's some help

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi All,

I have a multi-part issue and am not sure this is the correct forum, so
feel
free to move this post, as necessary. With that being said, allow me to
briefly (will go into more detail, if needed) describe my problem(s):

Recently, I was given a database to dissect and try to make sense of.
Currently, it is just a bunch of tables and queries... nothing more. One
of
the things the users of this database have asked is for the ability to see
object dependencies of their queries. Since this is a Access 2003
database,
my first suggestion was to use the built-in feature to display this
information... no go. They wanted something easier. They wanted to be
able
to select the name of an object from a combo box, click a command button,
have all dependencies displayed, and show whether the dependency is a
table
or query. After doing a little bit of research, here, as well as other
sites, I came up with something that semi-works, and now I need a little
bit
of help.

Ideally, I will have a form with a combo box for them to select the
object,
a button to run this code, and a text box (?) to display object dependency
info. I'm not sure if this is the best way to do this, but it's what I
was
able to come up with.

Option Compare Database
Option Explicit

Sub DependencyTest()

Dim depInf0 As DependencyInfo, depInf1 As DependencyInfo, depInf2 As
DependencyInfo, _
depInf3 As DependencyInfo, depInf4 As DependencyInfo
Dim obj0 As AccessObject, obj1 As AccessObject, obj2 As AccessObject,
obj3 As AccessObject, _
obj4 As AccessObject

Debug.Print [Forms]![frmObjectNames].[Form]![cboObjectNames]
Set depInf0 =
Application.CurrentData.AllQueries([Forms]![frmObjectNames].[Form]![cboObjectNames]).GetDependencyInfo
For Each obj0 In depInf0.Dependencies
If obj0.Type = acTable Then
Debug.Print " Sub 1: " & obj0.Name & " (TABLE)"
ElseIf obj0.Type = acQuery Then
Debug.Print " Sub 1: " & obj0.Name & " (QUERY)"
Else
Debug.Print " Sub 1: " & obj0.Name & " (UNION QUERY)"
End If
Set depInf1 = obj0.GetDependencyInfo
For Each obj1 In depInf1.Dependencies
If obj1.Type = acTable Then
Debug.Print " Sub 2: " & obj1.Name & " (TABLE)"
ElseIf obj1.Type = acQuery Then
Debug.Print " Sub 2: " & obj1.Name & " (QUERY)"
Else
Debug.Print " Sub 2: " & obj2.Name & " (UNION QUERY)"
End If
Set depInf2 = obj1.GetDependencyInfo
For Each obj2 In depInf2.Dependencies
If obj2.Type = acTable Then
Debug.Print " Sub 3: " & obj2.Name & "
(TABLE)"
ElseIf obj2.Type = acQuery Then
Debug.Print " Sub 3: " & obj2.Name & "
(QUERY)"
Else
Debug.Print " Sub 3: " & obj2.Name & " (UNION
QUERY)"
End If
Set depInf3 = obj2.GetDependencyInfo
For Each obj3 In depInf3.Dependencies
If obj3.Type = acTable Then
Debug.Print " Sub 4: " & obj3.Name &
"
(TABLE)"
ElseIf obj3.Type = acQuery Then
Debug.Print " Sub 4: " & obj3.Name &
"
(QUERY)"
Else
Debug.Print " Sub 4: " & obj3.Name &
"
(UNION QUERY)"
End If
Set depInf4 = obj3.GetDependencyInfo
For Each obj4 In depInf4.Dependencies
If obj4.Type = acTable Then
Debug.Print " Sub 5: " &
obj4.Name & " (TABLE)"
ElseIf obj4.Type = acQuery Then
Debug.Print " Sub 5: " &
obj4.Name & " (QUERY)"
Else
Debug.Print " Sub 5: " &
obj4.Name & " (UNION QUERY)"
End If
Next
Next
Next
Next
Next
End Sub

Basically, this code looks at the selection the user has made from
cboObjectNames (since this database only has tables and queries, I only
need
to look at the queries to see what they are dependant upon) to see what
they
are dependant upon. If the query is based on another query, it will
display
what that query is dependant upon, too. Currently, it will do this up to
5
levels deep.

So I'm sure you're dying to know what I need help on, right? Ok!

1. As you will notice, I have "Debug.Print" sprinkled in that code all
over
the place, so my results are only displayed in the immediate window.
Obviously, this is no good for the end user, but, unfortunately, I have NO
idea how to pull this information and display it on the form the user
will,
err, use. This is my biggest issue... if I can't get this to work, there
is
no point going to my next problem.

2. As I mentioned earlier, this code is only set to display dependancies
up
to 5 levels deep. Right now, that is fine as there are no queries that
have
that many nested one's, but you never know in the future... I would like
to
make this code more scalable, if possible.

3. All the queries are based off tables or other queries. Unfortunately,
several of these queries are union queries and those are not displayed at
all
when this code is run. Ideally, those would be displayed as well.

4. I want to be able to display what the object is. As you can see, if
the
object type is = acTable, (TABLE) is displayed... acQuery, (QUERY) is
displayed. I have coded a line for (UNION QUERY), but since those don't
display at all right now, just disregard those lines of code.

Here is a sample of what is displayed in the immediate window when this
code
is run:

DependencyTest
EXTRACT - LNI PUB - ANES - FOR CSV FILE
Sub 1: ~Anesthesia FS (TABLE)
Sub 1: LNI Conversion Factors (TABLE)
Sub 1: Fees - Publication, System, Appeal - LNI (QUERY)
Sub 2: COVERAGE LNI - Step 2 (QUERY)
Sub 3: ~Code Groupings (TABLE)
Sub 3: COVERAGE LNI - Step 1 (QUERY)
Sub 4: ~DATE (TABLE)
Sub 4: LNI Coverage (TABLE)
Sub 2: Possible PMDs, RVUs and Fees (QUERY)
Sub 3: ~Code Groupings (TABLE)
Sub 3: Possible Fees - Step 1-4 (QUERY)
Sub 3: Possible PMDs and Fees - ALL (QUERY)
Sub 4: Possible Fees - Step 7-1 (QUERY)
Sub 1: Valid Codes - Step 3-3 - LNI (QUERY)

I know that was a lot of information, but I probably didn't give enough.
If
anyone can help me with this problem, I would be very appreciative. If
you
DO need more info, don't hesitate to ask... I'll supply as much as I can.

Thanks!

Robert