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(strN ame)
Debug.Print "Table: ";
Case acQuery
Set AO = CurrentData.AllQueries(str Name)
Debug.Print "Query: ";
Case acForm
Set AO = CurrentProject.AllForms(st rName)
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.
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(strN
Debug.Print "Table: ";
Case acQuery
Set AO = CurrentData.AllQueries(str
Debug.Print "Query: ";
Case acForm
Set AO = CurrentProject.AllForms(st
Debug.Print "Form: ";
Case acReport
Set AO = CurrentProject.AllReports(
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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-->Databas e 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-->Objec t 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?
Database Tools--->Analyze-->Databas
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-->Objec
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?
ASKER
It pointed me in the right direction.