Link to home
Start Free TrialLog in
Avatar of dcmennealy
dcmennealy

asked on

Problem with getting code to create a list of object dependencies to work

I am trying to create a list of dependencies for several reports.  I am new to the VBA world on Microsoft Access but 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 got this code off of a website and the original code used a function called showdependencies but that function is not defined in access 2007.  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?

And I can't download any add-ins or software because this is a work computer and I don't have administrative rights to install anything.  I can open attachments though.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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
BTW,  the FMS products are well worth the cost.  A couple of hours of your wasted time will pay for the product, that is the argument that I've used in the past to get my boss to fund this type of purchase.
Also remember that Access 2007 has a built in Database Documenter.
Database Tools--->Analyze-->Database documenter.

Though not as robust as the FMS tool, it is free, and it may get you what you want.

Also note that Access also has a Dependency tool as well (in the same location):
Database Tools--->Show/Hide-->Object Dependencies
Though, this is just a screen, (not really a "Report"), but again, it might get you what you need for free.

Can you post the link where you got that code?
Avatar of dcmennealy
dcmennealy

ASKER

It pointed me in the right direction.