Link to home
Start Free TrialLog in
Avatar of dcmennealy
dcmennealy

asked on

Trying to create a dependency report in Access

I am relatively new to Access but pretty technically savy.  I am trying to create a dependency report for several reports contained in a database.  The database is very large and has many queries, make tables, tables, etc...  I have never successfully used code in Access but I found the code below  that someone said did what I am trying to do.  I created a module and entered this code but I am not sure how to run it in the database.  Also, I am not 100% sure it works.  The person who created the code didn't say what year he was working with (2000, 2003 or 2007, etc...).  I am using 2007 and I am not sure if the definitions are the same.  Can someone please tell me how to integrate the code with this module to run in the database?  Also, if there are any potential problems I could have as you read the code?  Thank you so much for your help in advance!

Public Sub ShowDependencies(TypeName As AcObjectType, strName As String)
  ' Show dependency information for the specified object
  Dim AO As AccessObject
  Dim AO2 As AccessObject
  Dim DI As DependencyInfo
  On Error GoTo HandleErr
  ' Get the AccessObject
  Select Case intType
    Case acTable
      Set AO = CurrentData.AllTables(strName)
      Debug.Print "Table: ";
    Case acQuery
      Set AO = CurrentData.AllQueries(strName)
      Debug.Print "Query: ";
    Case acForm
      Set AO = CurrentProject.AllForms(strName)
      Debug.Print "Form: ";
    Case acReport
      Set AO = CurrentProject.AllReports(strName)
      Debug.Print "Report: ";
  End Select
  Debug.Print strName
  ' Get the dependency info
  Set DI = AO.GetDependencyInfo()
  ' Print results
  If DI.Dependencies.Count = 0 Then
    Debug.Print "This object does not depend on any objects"
  Else
    Debug.Print "This object depends on these objects:"
    For Each AO2 In DI.Dependencies
      Select Case AO2.Type
        Case acTable
          Debug.Print "  Table: ";
        Case acQuery
          Debug.Print "  Query: ";
        Case acForm
          Debug.Print "  Form: ";
        Case acReport
          Debug.Print "  Report: ";
      End Select
      Debug.Print AO2.NAME
    Next AO2
  End If
  If DI.Dependants.Count = 0 Then
    Debug.Print "No objects depend on this object"
  Else
    Debug.Print "These objects depend on this object:"
    For Each AO2 In DI.Dependants
      Select Case AO2.Type
        Case acTable
          Debug.Print "  Table: ";
        Case acQuery
          Debug.Print "  Query: ";
        Case acForm
          Debug.Print "  Form: ";
        Case acReport
          Debug.Print "  Report: ";
      End Select
      Debug.Print AO2.NAME
    Next AO2
  End If
ExitHere:
  Exit Sub
HandleErr:
  MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
  Resume ExitHere
End Sub
Avatar of borki
borki
Flag of Australia image

Without running it, the code looks fine to me. Be aware the your output will not show in a form or report, but only in the debug window of the Access code environment.

To run the code, you can call it also from the debug window, you just type (for example)

Call ShowDependencies(acTable, "tblCustomer")

and hit enter.

If you are after a more "luxerious" front end, you could create a form with a list box which enumerates all your objects and list them in the form, along the lines of:

'List all forms
Dim accobj As AccessObject
Dim strDoc As String

For Each accobj In CurrentProject.AllForms
strDoc = accobj.Name
Next

Access has got some documentation tools built in, check them out for your version of Access.
There are commercial documentation tools out there that do all you want and more, so if you are after proper documentation, I would recommend to use one of of them.
Avatar of dcmennealy
dcmennealy

ASKER

How do I open the debug window?  When I am in the Access code environment (2007) debug is not one of the view options.
Avatar of Jim Dettman (EE MVE)

CTRL/G will bring up the window when your in the VBA editor.

JimD.
Thanks!  I ran the code and it doesn't recognize the showdependencies function.  The person who posted the code may have created that function themselves.  Do you know what I can use in place of it?  I tried getdependencyinfo (which is recognized by access) but it doesn't allow you to identify which object you want to get info about.  The only way I could get it to run was by replacing showdependencies with getdependencyinfo and this is what was returned:
Table:
This object does not depend on any objects
These objects depend on this object:
  Query: Exectutive Status Report - Real Estate Division

What am I doing wrong?  Is there any better code that you can think of that would work?
<< Is there any better code that you can think of that would work? >>

 I would suggest Rick Fisher's Find and Replace:

www.rickworld.com

  Cheap, and it does a lot more then just the dependicies.

JimD.
I can't download any software on my computer (work) but thanks for the suggestion.
OK, so I was able to get a result using the following code:

Public Function getdependencyinfo()
  ' Show dependency information for the specified object
  Dim AO As AccessObject
  Dim AO2 As AccessObject
  Dim DI As DependencyInfo
  On Error GoTo HandleErr
  ' Get the AccessObject
  Select Case intType
    Case acTable
      Set AO = CurrentData.AllTables(strName)
      Debug.Print "Table: ";
    Case acQuery
      Set AO = CurrentData.AllQueries(strName)
      Debug.Print "Query: ";
    Case acForm
      Set AO = CurrentProject.AllForms(strName)
      Debug.Print "Form: ";
    Case acReport
      Set AO = CurrentProject.AllReports(strName)
      Debug.Print "Report: ";
  End Select
  Debug.Print strName
  ' Get the dependency info
  Set DI = AO.getdependencyinfo()
  ' Print results
  If DI.Dependencies.Count = 0 Then
    Debug.Print "This object does not depend on any objects"
  Else
    Debug.Print "This object depends on these objects:"
    For Each AO2 In DI.Dependencies
      Select Case AO2.Type
        Case acTable
          Debug.Print "  Table: ";
        Case acQuery
          Debug.Print "  Query: ";
        Case acForm
          Debug.Print "  Form: ";
        Case acReport
          Debug.Print "  Report: ";
      End Select
      Debug.Print AO2.NAME
    Next AO2
  End If
  If DI.Dependants.Count = 0 Then
    Debug.Print "No objects depend on this object"
  Else
    Debug.Print "These objects depend on this object:"
    For Each AO2 In DI.Dependants
      Select Case AO2.Type
        Case acTable
          Debug.Print "  Table: ";
        Case acQuery
          Debug.Print "  Query: ";
        Case acForm
          Debug.Print "  Form: ";
        Case acReport
          Debug.Print "  Report: ";
      End Select
      Debug.Print AO2.NAME
    Next AO2
  End If
ExitHere:
  Exit Function
HandleErr:
  MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
  Resume ExitHere
End Function

but the result was

Table:
This object does not depend on any objects
These objects depend on this object:
  Query: Exectutive Status Report - Real Estate Division

I believe the original code would have worked if I had a definition for showdependencies but as I said before, I got this code online and it may have been a function that the user had defined themselves.  I need to basically be able to identify an object for the code to work.  I just don't know how.  Any ideas?
You are looking at a recursive function. "Showdependencies" calls itself while executing - there is no code missing from the source you found on the net. Also, I note that you have correctly replaced that name in your own adaption of the code.

The code is not producing the desired output. I think you ned to tell us how you are calling the function.

I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER CERTIFIED SOLUTION
Avatar of dcmennealy
dcmennealy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I found what I was looking for through my own research.