[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-22
4
Medium Priority
?
456 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:dcmennealy
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Dale Fye earned 1500 total points
ID: 35448244
You might want to look at Total Access Analyzer and Total Access Detective, by FMS.

http://www.fmsinc.com/Products/

Disclaimer: I have not relationship with FMS
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35448256
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35460084
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?
0
 

Author Closing Comment

by:dcmennealy
ID: 35725691
It pointed me in the right direction.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question