Solved GetDependencyInfo method not recognised by access 2016

J

John Sh

Active member
Local time
Tomorrow, 02:44
Joined
Feb 8, 2021
Messages
594
I'm diving a bit deeper than usual, trying to create a list of dependencies but the method "GetDependencyInfo()" is not recognised.
Is there a reference library to enable this method?
 
How are you calling it? Can you post a screenshot of the error you get?

Have you checked the help file?

Remarks​

This method will return a run-time error if any of the following conditions are true:

  • The Track name AutoCorrect info setting (Tools menu > Options dialog box > General tab) is disabled. Use the following code to enable the Track name AutoCorrect info setting and update the dependency information for all of the objects in the database: Application.SetOption "Track Name AutoCorrect Info", 1
  • You have insufficient permissions to check the dependency information for the specified AccessObject object.
  • This method is being called from an Access project (.adp).
Access does not search Visual Basic for Applications (VBA) code, macros, or data access pages for dependencies.
Click to expand...
 
cheekybuddha said:
How are you calling it? Can you post a screenshot of the error you get?

Have you checked the help file?
Click to expand...
I'm not getting a runtime error, it's a compile error.
Screenshot_10.jpg
 
What happens if you change your declarations to:
Code: 
Dim tdf As DAO.Tabledef
Dim rs As DAO.Recordset
 
cheekybuddha said:
What happens if you change your declarations to:
Code: 
Dim tdf As DAO.Tabledef
Dim rs As DAO.Recordset
Click to expand...
I have altered the code to

Code: 
Private Sub getDependency()
    Dim sSql As String
    Dim tdf As dao.TableDef
    Dim rs As dao.Recordset
    Dim db As Database
    Set db = CurrentDb
    DoCmd.RunSQL "DELETE * FROM Depend_Data"
    Set rs = db.OpenRecordset("Depend_Data", dbOpenDynaset)
    For Each tdf In db.TableDefs
        If InStr(tdf.Name, "~") = 0 Then
            rs.AddNew
                rs!TableName = tdf.Name
                rs!Depends = tdf.GetDependencyInfo
            rs.Update
        End If
    Next tdf
End Sub

With the same result.

I am assuming that declaring tdf as tabledef means that tdf is an object that is required by GetDependencyInfo()

Given the function "oDB() is declared as dao, there should not be any change in behaviour.

Code: 
Public Function oDB() As dao.Database
    If pCurrentDb Is Nothing Then
        Set pCurrentDb = CurrentDb
    End If
    Set oDB = pCurrentDb
End Function
 
The fact that dao does not automatically update to DAO suggests something might be off, like a missing reference or a mis-declared variable.

Do you have Option Explicit declared at the top of every code module?

Does your code compile?
 
Perhaps this old thread will help

GetDependencyInfo Method

Is anyone familiar with the use of this? The help files don't provide anything that looks like an example of its use. The help files say: "Returns a DependencyInfo object that represents the database objects that are dependent upon the specified object. expression.GetDependencyInfo()...
www.access-programmers.co.uk
 
cheekybuddha said:
The fact that dao does not automatically update to DAO suggests something might be off, like a missing reference or a mis-declared variable.

Do you have Option Explicit declared at the top of every code module?

Does your code compile?
Click to expand...
Yes, Compare datbase and Option Explicit is at the top of each and every modiule,
and no, the code does not compile.
 
Reading through that "old" code, it would seem that "tdf as tabledef" does not create an object.
The declaration there is "Dim objTable As Access.AccessObject"
and the call is "objTable.GetDependencyInfo.Dependants".
The "obj" reference seems to be critical.
I will modify that old code and see what happens.
 
cheekybuddha said:
Does your code compile if you comment out your getDependency() sub?
Click to expand...
Yes, I have used that code to list table names and fields as well as listing queries and their sql.

This is the full, working, code before I added the "dependecies" procedure
Code: 
Option Compare Database
Option Explicit

Private Sub btnstart_Click()
    DoCmd.SetWarnings False
        GetTableData
        getQueryData
'        getDependency
    DoCmd.SetWarnings True
    Me.btnStart.Caption = "Done"
    Pause 3
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub GetTableData()
    Dim sSql As String
    Dim tdf As TableDef
    Dim x As Integer
    Dim rs As Recordset
    DoCmd.RunSQL "DELETE * FROM Table_Data"
    Set rs = oDB.OpenRecordset("Table_Data", dbOpenDynaset)
    For Each tdf In oDB.TableDefs
        If InStr(tdf.Name, "~") = 0 Then  ' Don't enumerate the system tables
            For x = 0 To tdf.Fields.Count - 1
                If InStr(tdf.Fields(x).Name, "main") > 0 Or InStr(tdf.Fields(x).Name, " ") > 0 Then
                    rs.AddNew
                    rs!TableName = tdf.Name
                    rs!FieldName = tdf.Fields(x).Name
                    rs.Update
                End If
            Next x
        End If
    Next tdf
End Sub


Private Sub getQueryData()
    Dim i As Integer
    Dim rs As Recordset
    DoCmd.RunSQL "DELETE * FROM Query_Data"
    Set rs = oDB.OpenRecordset("Query_Data", dbOpenDynaset)
    On Error Resume Next
    For i = 0 To oDB.QueryDefs.Count - 1
        If InStr(oDB.QueryDefs(i).Name, "~") = 0 Then
            rs.AddNew
                rs!query_name = oDB.QueryDefs(i).Name
                rs!query_text = oDB.QueryDefs(i).SQL
            rs.Update
        End If
    Next
End Sub
 
John Sh said:
Yes, Compare datbase and Option Explicit is at the top of each and every modiule,
and no, the code does not compile.
Click to expand...
I've just done a compact and repair and the doa stays in lower case.
I've attached a screen shot of the references
Screenshot_15.jpg
.
 
John Sh said:
it would seem that "tdf as tabledef" does not create an object.
Click to expand...
It does create an object (TableDef object), it just doesn't create an AccessObject object.

You have to access your tables via Application.CurrentData.AllTables.Item("TableName")
(or loop the AllTables collection)
 
John Sh said:
I've just done a compact and repair and the doa stays in lower case.
Click to expand...
Perhaps you have a local variable in your code somewhere named dao?

Otherwise, a de-compile / re-compile will probably fix it.
 
cheekybuddha said:
It does create an object (TableDef object), it just doesn't create an AccessObject object.

You have to access your tables via Application.CurrentData.AllTables.Item("TableName")
(or loop the AllTables collection)
Click to expand...
That compiles but I now get run time error 438.
I have tried with a string variable holding the table name and tdf.name. See the code.
It loops past the system and temp "~" tables then seems to be searching and eventually crashes on the first table.

Question, will this work on linked tables or do I need to go to the back end?

Code: 
Private Sub getDependency()
    Dim sStr As String
    Dim tdf  As TableDef
    Dim rs   As Recordset
'    Dim db   As Database
'    Set db = CurrentDb
    DoCmd.RunSQL "DELETE * FROM Depend_Data"
    Set rs = oDB.OpenRecordset("Depend_Data", dbOpenDynaset)
    For Each tdf In oDB.TableDefs
        If InStr(tdf.Name, "~") = 0 Then
            sStr = tdf.Name
            rs.AddNew
                rs!TableName = tdf.Name
                rs.Update
                rs!Depends = Application.CurrentData.AllTables.item(sStr).GetDependencyInfo.dependency
'                rs!Depends = Application.CurrentData.AllTables.item(tdf.Name).GetDependencyInfo.dependency
            rs.Update
        End If
    Next tdf
End Sub
 
John Sh said:
That compiles but I now get run time error 438.
I have tried with a string variable holding the table name and tdf.name. See the code.
It loops past the system and temp "~" tables then seems to be searching and eventually crashes on the first table.

Question, will this work on linked tables or do I need to go to the back end?
Code: 
Private Sub getDependency()
    Dim sStr As String
    Dim tdf  As TableDef
    Dim rs   As Recordset
'    Dim db   As Database
'    Set db = CurrentDb
    DoCmd.RunSQL "DELETE * FROM Depend_Data"
    Set rs = oDB.OpenRecordset("Depend_Data", dbOpenDynaset)
    For Each tdf In oDB.TableDefs
        If InStr(tdf.Name, "~") = 0 Then
            sStr = tdf.Name
            rs.AddNew
                rs!TableName = tdf.Name
                rs.Update
                rs!Depends = Application.CurrentData.AllTables.item(sStr).GetDependencyInfo.dependency
'                rs!Depends = Application.CurrentData.AllTables.item(tdf.Name).GetDependencyInfo.dependency
            rs.Update
        End If
    Next tdf
End Sub
Click to expand...
The most effective way to answer that question might be to go to the back end and try it. Then you could report what actually happens.
 
GPGeorge said:
The most effective way to answer that question might be to go to the back end and try it. Then you could report what actually happens.
Click to expand...
I'm half way there, doing a decompile recompile at the moment but will certainly post the results as I find them
 
John Sh said:
That compiles but I now get run time error 438.
I have tried with a string variable holding the table name and tdf.name. See the code.
It loops past the system and temp "~" tables then seems to be searching and eventually crashes on the first table.

Question, will this work on linked tables or do I need to go to the back end?

Code: 
Private Sub getDependency()
    Dim sStr As String
    Dim tdf  As TableDef
    Dim rs   As Recordset
'    Dim db   As Database
'    Set db = CurrentDb
    DoCmd.RunSQL "DELETE * FROM Depend_Data"
    Set rs = oDB.OpenRecordset("Depend_Data", dbOpenDynaset)
    For Each tdf In oDB.TableDefs
        If InStr(tdf.Name, "~") = 0 Then
            sStr = tdf.Name
            rs.AddNew
                rs!TableName = tdf.Name
                rs.Update
                rs!Depends = Application.CurrentData.AllTables.item(sStr).GetDependencyInfo.dependency
'                rs!Depends = Application.CurrentData.AllTables.item(tdf.Name).GetDependencyInfo.dependency
            rs.Update
        End If
    Next tdf
End Sub
Click to expand...

I'd imagine you'd want something more like:
Code: 
Private Sub getDependency()
    Dim sStr As String
    Dim tbl  As Access.AccessObject
    Dim rs   As DAO.Recordset
'    Dim db   As Database
'    Set db = CurrentDb
    DoCmd.RunSQL "DELETE * FROM Depend_Data"
    Set rs = oDB.OpenRecordset("Depend_Data", dbOpenDynaset)
    For Each tbl In Application.CurrentData.AllTables
        If InStr(tbl.Name, "~") = 0 Then
            sStr = tbl.Name
            rs.AddNew
                rs!TableName = sStr
                rs!Depends = tbl.GetDependencyInfo.dependency   ' <-- I don't know whether this is correct
            rs.Update
        End If
    Next tdf
    rs.Close
    Set rs = Nothing
End Sub
 
GPGeorge said:
The most effective way to answer that question might be to go to the back end and try it. Then you could report what actually happens.
Click to expand...
Two things happened.
when I transferred the form to the back end and added the required references, the lower case dao automatically turned to upper case.
I might have to reload the references.
Running the procedure created the same 438 error but the search was much faster. .5 seconds for the first table against about 10 to 15 seconds in the split tables.
I'm off to bed now so will try the "old" code in the morning.
In the meantime I thank you all for your input and interest in this post.
Thank you.
John
 
cheekybuddha said:
I'd imagine you'd want something more like:
Code: 
Private Sub getDependency()
    Dim sStr As String
    Dim tbl  As Access.AccessObject
    Dim rs   As DAO.Recordset
'    Dim db   As Database
'    Set db = CurrentDb
    DoCmd.RunSQL "DELETE * FROM Depend_Data"
    Set rs = oDB.OpenRecordset("Depend_Data", dbOpenDynaset)
    For Each tbl In Application.CurrentData.AllTables
        If InStr(tbl.Name, "~") = 0 Then
            sStr = tbl.Name
            rs.AddNew
                rs!TableName = sStr
                rs!Depends = tbl.GetDependencyInfo.dependency   ' <-- I don't know whether this is correct
            rs.Update
        End If
    Next tdf
    rs.Close
    Set rs = Nothing
End Sub
Click to expand...
rs!Depends = tbl.GetDependencyInfo.dependency gives method not recognised as a compile error
if I use rs!Depends = Application.CurrentData.AllTables.item(tdf.Name).GetDependencyInfo.dependency
it still gives 438
 
You must log in or register to reply here.

Similar threads

J
Solved Variable not defined in sql string
Replies
11
Views
226
John Sh
J
J
Solved Variable in HAVING clause
Replies
8
Views
347
John Sh
J
T
Access When emailing brings up Outlook 2016 wizard to add email account
Replies
10
Views
442
The_Doc_Man
The_Doc_Man
S
Two ways to access controls on other forms
Replies
11
Views
487
Pat Hartman
P
K
Solved Freeze the recordset of a search form
2
Replies
33
Views
1,342
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom