Solved GetDependencyInfo method not recognised by access 2016

[cheekybuddha]

What is error 438?

 

[GPGeorge]

It means the object doesn't support the property or method.

Object doesn't support this property or method (Error 438)

Office VBA reference topic

learn.microsoft.com

 

[cheekybuddha]

Try running this in the Immediate Window (ctrl + G):

?ListTableDependencies

with this function in a standard module:

Function ListTableDependencies() As Boolean
    
  Dim tbl       As Access.AccessObject
  Dim dep       As Access.AccessObject
  Dim depCount  As Integer
  Dim depOthers As Integer
  Dim otherDeps As String
  Dim i         As Integer
  Dim j         As Integer
 
  For Each tbl In Application.CurrentData.AllTables
    Select Case True
    Case tbl.Name Like "msys*"      ' ignore system tables
    Case tbl.Name Like "usys*"      ' ignore user system tables
    Case tbl.Name Like "*~*"        ' ignore temporary tables
    Case Else
      Debug.Print tbl.Name
      depCount = tbl.GetDependencyInfo.Dependencies.Count
      Debug.Print "Dependencies.Count:", depCount
      If depCount > 0 Then
        i = 1
        For Each dep In tbl.GetDependencyInfo.Dependencies
          depOthers = dep.GetDependencyInfo.Dependencies.Count - depCount
          otherDeps = vbNullString
          If depOthers > 0 Then
            For j = 0 To dep.GetDependencyInfo.Dependencies.Count - 1
              If dep.GetDependencyInfo.Dependencies(j).Name <> tbl.Name Then
                otherDeps = otherDeps & ", " & dep.GetDependencyInfo.Dependencies(j).Name
              End If
            Next j
            otherDeps = "(also depended on by " & depOthers & " other table(s): " & Mid(otherDeps, 3) & ")"
          End If
          Debug.Print i, dep.Name, otherDeps
          i = i + 1
        Next
      End If
      Debug.Print
    End Select
  Next tbl
  ListTableDependencies = Err = 0
 
End Function

If the output of that looks correct, then we can work out getting it in to your table

 

[isladogs]

This code will list all dependenices and dependant objects for a specified object to the immediate window
It is the first part of a dependency module in my DatabaseAnalyzerPro app

Public Sub ShowDependencies(intType As AcObjectType, strName As String)

' Show dependency information for the specified object in the immediate window
Dim ao As AccessObject
Dim AO2 As AccessObject
Dim DI As DependencyInfo

On Error GoTo Err_Handler

' 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
   
Exit_Handler:
    Exit Sub
   
Err_Handler:
    MsgBox "Error " & Err.Number & " in ShowDependencies procedure : " & Err.Description, vbCritical
    Resume Exit_Handler
   
End Sub

Example usage:

ShowDependencies acTable, "tblObjectInfo"

Example output

Table: tblObjectInfo
This object does not depend on any objects
These objects depend on this object:
  Query: qryCodeModuleObjects
  Query: qryMSysObjectsEXT
  Query: qryTotalSize
  Form: fsubObjectsInfo
  Form: fsubObjMetadata
  Form: fsubTableInfo
  Report: rptFieldInfo
  Report: rptMetadata
  Report: rptTableInfo
  Report: rsubFieldInfo
  Report: rsubMetadata
  Report: rsubObjectCount
  Report: rsubTableInfo

No additional references are required
You should easily be able to adapt this to loop through all tables (etc) and save the data obtained. I'll leave that to you

 

[John Sh]

This code will list all dependenices and dependant objects for a specified object to the immediate window
It is the first part of a dependency module in my DatabaseAnalyzerPro app

Thank you, thank you, thank you.
Reading through your code the problem is the way I am addressing things. I was concerned that I was not declaring objects.
Modifying the code to save to a file only requires a recordset.
Once again, thank you.
John

 

[isladogs]

You’re welcome.
In my own app, I also use a recordset to then loop through all tables, queries, forms and reports in a specified external database and save the data to a local table. The Get dependency info method doesn't work for modules or macros

 

[John Sh]

You’re welcome.
In my own app, I also use a recordset to then loop through all tables, queries, forms and reports in a specified external database and save the data to a local table. The Get dependency info method doesn't work for modules or macros

It's taken a little while to get all the bugs out and remove redundant code.
So here is my take on the problem.

If you run this code an a split database it will take an hour, or more, depending on the size of the DB.
On the same non_split DB it takes but a few minutes.

Many, many thanks to all who gave of their time and knowledge.
John

 

[CJ_London]

you might find reseting oDb on each interation is a factor in your performance

Public Function oDB() As DAO.Database
    If pCurrentDb Is Nothing Then
        Set pCurrentDb = CurrentDb
    End If
    Set oDB = pCurrentDb
End Function

Not tested, and perhaps you have already tried it but you might find this version is quicker since it only needs to be set once

Public Function oDB() As DAO.Database

    If oDb Is Nothing Then Set oDb = CurrentDb

End Function

 

[John Sh]

Not tested, and perhaps you have already tried it but you might find this version is quicker since it only needs to be set once

Public Function oDB() As DAO.Database

    If oDb Is Nothing Then Set oDb = CurrentDb

End Function

The original code came from the likes of "DevHut" or Daniel Pinault and came with explanations and test info.
The idea is that it only sets pCurrentdb = currentdb once, this is the time taker. it then sets new instances of "oDB" to pCurrent, which does not require the overheads of current db.
The other difference is that one is using the variable "oDB" and not the function "oDB()"

The system is called "SHOV" and includes the following code that has "Private pFSO As Scripting.FileSystemObject".
According to the author, the time savings over a large test run was considerabe when compared to the usual set x as currentdb.

This is the full code along with the two private variables "pCurrent" and "pFSO and two "Killer" functions.

My apologies to the original author.

Public Function oDB() As DAO.Database
    If pCurrentDb Is Nothing Then
        Set pCurrentDb = CurrentDb
    End If
    Set oDB = pCurrentDb
End Function

Public Sub oDB_Clear()
    Set pCurrentDb = Nothing
End Sub

Public Function oFSO() As Scripting.FileSystemObject
    If pFSO Is Nothing Then
        Set pFSO = CreateObject("Scripting.FileSystemObject")
    End If
    Set oFSO = pFSO
End Function

Public Sub oFSO_Clear()
    Set pFSO = Nothing
End Sub

 

[isladogs]

You may find this article interesting:

Speed Comparison Tests 2

This article compares the speed of four methods used to reference the current database - CurrentDb vs DBEngine(0)(0) vs a custom property ThisDb and a custom function CurDb. It is the second in a series of articles discussing various tests done to compare the efficiency of different approaches...

www.isladogs.co.uk

 

[John Sh]

You may find this article interesting:

Speed Comparison Tests 2

This article compares the speed of four methods used to reference the current database - CurrentDb vs DBEngine(0)(0) vs a custom property ThisDb and a custom function CurDb. It is the second in a series of articles discussing various tests done to compare the efficiency of different approaches...

www.isladogs.co.uk

Thanks, I'll check it out.
The system I've written is not overly large, but running on a wireless university network is slow, so any speed advantage I can get is useful.

 

[John Sh]

You may find this article interesting:

Speed Comparison Tests 2

This article compares the speed of four methods used to reference the current database - CurrentDb vs DBEngine(0)(0) vs a custom property ThisDb and a custom function CurDb. It is the second in a series of articles discussing various tests done to compare the efficiency of different approaches...

www.isladogs.co.uk

So, at the end of the day, it makes little, or no, difference to the overall time of a procedure.

 

[tvanstiphout]

running on a wireless university network is slow

Copy the BE to your local machine, just to run your procedure on. Although copying takes time too, that should be MUCH less overhead than pulling the data across the network in your procedure.

Also worth running the Performance tab of Task Manager while running your code. You can see if the network is maxed out, or the disk, or the cpu.

 

[John Sh]

Copy the BE to your local machine, just to run your procedure on. Although copying takes time too, that should be MUCH less overhead than pulling the data across the network in your procedure.

Also worth running the Performance tab of Task Manager while running your code. You can see if the network is maxed out, or the disk, or the cpu.

This is not an option as we have multiple front ends running simultaneously.

 

[isladogs]

So, at the end of the day, it makes little, or no, difference to the overall time of a procedure.

The time saved is likely to only be a minute fraction of the time needed to run the analysis. However, as the saying goes, every little helps.
So I do use Set Db=CurrentDb, at the start of the any lengthy analysis process

The overall time will increase dramatically if there are a large number of objects to be checked
Say you have a total of N tables, queries, forms & reports.
The dependency subroutine needs to check every one of those N objects against all (N-1) others . . . and to do the twice - once for possible dependant objects and again for objects each depends on
So the total number of steps = 2*N*(N-1)

If N = 100, total steps = 2*100*99 = 19800
If N = 1000, total steps = 2*1000*990 = 1,980,000 i.e. 100x larger

for that reason, i include a warning before running the dependency check when N > 250:

For that particular database where N = 3468, total steps = 24,047,112. Not surprisingly, it took a a couple hours or so to complete.
Added to that, I use a progress bar for information which adds about 14% to the total time needed!

The dependency info check is by far the slowest part of the overall analysis for large databases

 

[John Sh]

The dependency subroutine needs to check every one of those N objects against all (N-1) others . . . and to do the twice - once for possible dependant objects and again for objects each depends on
So the total number of steps = 2*N*(N-1)

Which is why, in my variation, I "Set DI = ao.GetDependencyInfo()" once at the beginning of the getdependencies sub and then check for dependents and dependencies without the need for a second "Set DI = ao.GetDependencyInfo()". The reduction in time taken is obvious.

BTW> i will soon post an updated version that makes the resultant tables more readable.

 

[isladogs]

Which is why, in my variation, I "Set DI = ao.GetDependencyInfo()" once at the beginning of the getdependencies sub and then check for dependents and dependencies without the need for a second "Set DI = ao.GetDependencyInfo()". The reduction in time taken is obvious.

The way you wrote that suggested that I had set it twice in my code! Glad to say I hadn't!

 

[John Sh]

Apologies, I misread your code.

I do have a problem though.
when checking a form, the first dependency is a table and returns error 2002.
AO2.name is the name of a table but AO shows nothing

                If DI.Dependencies.Count = 0 Then
                    rs.AddNew
                        rs!table_name = sTableType & sTable & ":  does not depend on any objects"
                    rs.Update
                Else
                    rs.AddNew
                        rs!depends_on = getType(AO2) & AO2.Name  >>>>>>>>>  from here
                    rs.Update
                End If
            End If
nextDepend:
        Next AO2
    End If
    rs.Close
    Set rs = Nothing
End Sub

Private Function getType(AO2 As AccessObject) As String
    Select Case AO2.Type      >>>>>>>>>> here  error 2002
        Case acTable:
            getType = "Table:  "
        Case acQuery:
            getType = "Query:  "
        Case acForm:
            getType = "Form:  "
        Case acReport:
            getType = "Report:  "
    End Select
End Function

 

[isladogs]

It would help if you stated what the error description is:

?AccessError(2002)
You tried to perform an operation involving a function or feature that was not installed in this version of (app name)

Although your code is based on mine, you have made changes to what I posted in order to loop through & save to a table.
I can't tell why you are getting error 2002
What happens if you bypass that error using ResumeNext?

 

[John Sh]

It would help if you stated what the error description is:


Although your code is based on mine, you have made changes to what I posted in order to loop through & save to a table.
I can't tell why you are getting error 2002
What happens if you bypass that error using ResumeNext?

The resume next "solved" the problem but the form's recordsource is not recorded. I'll play with this for a while.
There is a problem with "currenproject.allforms" and records in that the listing is completely random.
I have added a sub, "SortIt" that adds the form names to a recordset .
The "CurrentProject.AllForms(strName)" strname is taken from the sorted recordset