VBA to Display References, Access 2000 through 2019 (1 Viewer)

[JGravesNBS]

I Need Microsoft Access VBA code to display active mdb database Reference information in the Immediate Window

Here's what I have:

Sub ShowReferencesInImmediateWindow()
Dim ref As Reference
For Each ref In Application.CurrentDb.References
Debug.Print ref.Name & " - " & ref.FullPath
Next ref
End Sub

When I run the Sub I get the following error with Reference highlighted:

Microsoft Visual Basic for Applications
Compile error: Method or data member not found
For Each ref In Application.CurrentDb.References

I need this to run on Access 2000 through Access 2019

11397335

 

[Josef P.]

For Each ref In Application.References

 

[Gasman]

Now cross posted https://www.utteraccess.com/topics/2065239?post=unread

 

[Pat Hartman]

This code puts the references in a table so they are easier to view. If you prefer the debug print, just change the code to print instead of addingrecords to a table.

Sub ReferencePropertiesPring()
    Dim ref As Reference
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TS1 As DAO.Recordset
    Dim ThisDB As DAO.Database
    
    On Error GoTo Err_ReferenceProperties
    
    Set ThisDB = CurrentDb()
  
    ThisDB.Containers.Refresh

    Set QD1 = ThisDB.QueryDefs!qDeltblReferences
        QD1.Execute
    Set TD1 = ThisDB.TableDefs!tblReferences
    Set TS1 = TD1.OpenRecordset

    ' Enumerate through References collection.
    For Each ref In References
        ' Check IsBroken property.
        TS1.AddNew
        TS1!RefName = ref.Name
        TS1!RefFullPath = ref.FullPath
        TS1!RefVersion = ref.Major & "." & ref.Minor
        TS1!RefGUID = ref.Guid
        TS1!RefKind = ref.Kind
        
        If ref.IsBroken = False Then
            TS1!RefBroken = 0
            'Debug.Print "Name: ", ref.Name
            'Debug.Print "FullPath: ", ref.FullPath
            'Debug.Print "Version: ", ref.Major & "." & ref.Minor
        Else
            Debug.Print "GUIDs of broken references:"
            Debug.Print ref.Guid
            TS1!RefBroken = -1
        End If
        TS1.Update
Next ref
Exit_ReferenceProperties:
    ThisDB.Close
    Exit Sub

Err_ReferenceProperties:
    Select Case Err.Number
        Case 3043
            MsgBox "Please select a valid database", vbOKOnly
        Case 91   ' db was not opened so it cannot be closed.
            Exit Sub
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_ReferenceProperties
    Resume
End Sub

 

[JGravesNBS]

For Each ref In Application.References

Thanks for the feedback

The code change worked fine on Access 2000, output the six records, no error message

When run on Access 2016 it put out the six references but generated an error

VBA - C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
Access - C:\Program Files (x86)\Microsoft Office\root\Office16\MSACC.OLB
stdole - C:\Windows\SysWOW64\stdole2.tlb
DAO - C:\Program Files (x86)\Common Files\Microsoft Shared\DAO\dao360.dll
VBIDE - C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Office - C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\MSO.DLL

Run-time error '-2147319779 (8002801d)'
Method 'FullPath' of object 'Reference' failed

Debug.Print ref.Name & " - " & ref.FullPath

Any thoughts?

 

[The_Doc_Man]

Any thoughts?

In the DB where that error occurred, open the list of references manually and see how many are actually checked.

If it is six and you still got an error, your loop terminated incorrectly.

If it is more than six, then look more carefully at the seventh reference to see if something is wrong with it.

 

[JGravesNBS]

There should be nine references:

1-Visual Basic For Applications
Location:
Language:English/Standard
C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL

2-Microsoft Access 16.0 Object Library
Location:
C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE

3-OLE Automation
Location:
C\:Windows SysWOW64\stdole2.tib

4-Microsoft DAO 3.6 Object Library
Location:
C:\Program Files (x86)\Common Files\Microsoft Shared\DAO\dao.360.dll

5-Microsoft Visual Basic for Applications Extensibility 5.3
Location:
C:\Program Files (x86)\Common Files\Microsoft Shared\VBA???

6-Microsoft Office 16.0 Object Library
Location:
C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\MSO.DLL

7-Microsoft Windows Common Controls 5.0 (SP2)
Location:
C:\WINDOWS\system32\comctl32.ocx

8-Microsoft Word 16.0 Object Library
Location:
C:\Program Files (x86)\Microsoft Office\root\Office16\MSWORD.OLB

9-Microsoft ActiveX Data Objects 2.1 Library
Location:
C:\Program Files (x86)\Common Files\System\ado\msado21.tlb

the Access 2016 computer I ran it on that listed six,

I ran it on a Access 2013 computer, listed all nine, no errors

 

[jdraw]

Have you tried using Isladogs(Colin) Access Windows Version Reference Checker?

 

[JGravesNBS]

Have you tried using Isladogs(Colin) Access Windows Version Reference Checker?

Thanks, I’ll take aLook

 

[JGravesNBS]

This code puts the references in a table so they are easier to view. If you prefer the debug print, just change the code to print instead of addingrecords to a table.

Sub ReferencePropertiesPring()
    Dim ref As Reference
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TS1 As DAO.Recordset
    Dim ThisDB As DAO.Database
   
    On Error GoTo Err_ReferenceProperties
   
    Set ThisDB = CurrentDb()
 
    ThisDB.Containers.Refresh

    Set QD1 = ThisDB.QueryDefs!qDeltblReferences
        QD1.Execute
    Set TD1 = ThisDB.TableDefs!tblReferences
    Set TS1 = TD1.OpenRecordset

    ' Enumerate through References collection.
    For Each ref In References
        ' Check IsBroken property.
        TS1.AddNew
        TS1!RefName = ref.Name
        TS1!RefFullPath = ref.FullPath
        TS1!RefVersion = ref.Major & "." & ref.Minor
        TS1!RefGUID = ref.Guid
        TS1!RefKind = ref.Kind
       
        If ref.IsBroken = False Then
            TS1!RefBroken = 0
            'Debug.Print "Name: ", ref.Name
            'Debug.Print "FullPath: ", ref.FullPath
            'Debug.Print "Version: ", ref.Major & "." & ref.Minor
        Else
            Debug.Print "GUIDs of broken references:"
            Debug.Print ref.Guid
            TS1!RefBroken = -1
        End If
        TS1.Update
Next ref
Exit_ReferenceProperties:
    ThisDB.Close
    Exit Sub

Err_ReferenceProperties:
    Select Case Err.Number
        Case 3043
            MsgBox "Please select a valid database", vbOKOnly
        Case 91   ' db was not opened so it cannot be closed.
            Exit Sub
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_ReferenceProperties
    Resume
End Sub

Thanks, I’ll take aLook

 

[The_Doc_Man]

When you manually examine the references, do you see anything like "missing" or "broken" in the dialog box that controls references?

If not, try this: Select (by single click) the 7th reference. You have an Up arrow and a Down arrow on the references mini-form. Use the Down arrow to move that 7th reference to last.

Next, re-run the code to show the references to see if it now shows eight items. That would tell us that the problem is in that reference.

If in fact moving the reference to the bottom of the list changes behavior of that code, then the problem is in the reference you moved. The usual approach is to cancel the reference, close the DB completely, then re-open and attempt to re-assert the reference to see if it helps.

 

[Gasman]

I used these, albeit for Excel, but should work in Access?

Sub xlfVBEListReferences()
' Requires References :: Microsoft Visual Basic for Applications Extensibility 5.3
'                        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Dim oRef As VBIDE.Reference   ' Item
Dim oRefs As VBIDE.References ' Collection
Dim i As Integer
 
Set oRefs = Application.VBE.ActiveVBProject.References
 
    Debug.Print "Print Time: " & Time & " :: Item - Name and Description"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.Name, oRef.Description, oRef.Major, oRef.Minor
        Next oRef
    Debug.Print vbNewLine
 
    i = 0
    Debug.Print "Print Time: " & Time & " :: Item - Full Path"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.FullPath
        Next oRef
    Debug.Print vbNewLine
 
    i = 0
    ' List the Globally Unique Identifier (GUID) for each library referenced in the current project
    Debug.Print "Print Time: " & Time & " :: Item - GUID"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.Guid
        Next oRef
    Debug.Print vbNewLine
 
End Sub
Sub xlfVBEAddReferences()
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References
 
    On Error GoTo OnError
    oRefs.AddFromFile "C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\MSO.DLL"
 
OnError:
End Sub
Sub xlfVBEAddReferencesGUID()
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References
 
    On Error GoTo OnError
        ' Syntax: AddFromGuid(Guid, Major, Minor)
        ' The Major version number of the reference.
        ' The Minor version number of the reference.
        ' Microsoft XML, v6.0 :: Major - 6, Minor - 0
    oRefs.AddFromGuid "{F5078F18-C551-11D3-89B9-0000F81FE221}", 6, 0
 
OnError:
End Sub
Sub xlfVBERemoveReference1()
Dim oRef As Reference
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References
Dim i As Integer
    For Each oRef In oRefs
        i = i + 1
        If oRef.Name = "Office" Then
        
            oRefs.Remove oRef
            'Debug.Print oRefs.Item(i).Name & " - " & oRefs.Item(i).Type
            Exit For
        End If
    Next oRef
 
End Sub
Sub xlfVBERemoveReference2()
Dim oRef As Reference
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References
 
    For Each oRef In oRefs
        If oRef.Description = "Microsoft Office 12.0 Object Library" Then
            oRefs.Remove oRef
            Exit For
        End If
    Next oRef
 
End Sub

Just run it and got

Print Time: 08:29:53 :: Item - Name and Description
Item 1        VBA           Visual Basic For Applications              4             0
Item 2        Access        Microsoft Access 12.0 Object Library       9             0
Item 3        stdole        OLE Automation               2             0
Item 4        DAO           Microsoft Office 12.0 Access database engine Object Library            12            0
Item 5        VBIDE         Microsoft Visual Basic for Applications Extensibility 5.3              5             3


Print Time: 08:29:53 :: Item - Full Path
Item 1        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Item 2        C:\Program Files (x86)\Microsoft Office\Office12\MSACC.OLB
Item 3        C:\Windows\SysWOW64\stdole2.tlb
Item 4        C:\PROGRA~2\COMMON~1\MICROS~1\OFFICE12\ACEDAO.DLL
Item 5        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB


Print Time: 08:29:53 :: Item - GUID
Item 1        {000204EF-0000-0000-C000-000000000046}
Item 2        {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
Item 3        {00020430-0000-0000-C000-000000000046}
Item 4        {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}
Item 5        {0002E157-0000-0000-C000-000000000046}

 

[Josef P.]

test code:

Sub ShowReferencesInImmediateWindow()
    Dim ref As Reference
    For Each ref In Application.References
        Debug.Print ref.Name,
        Debug.Print ref.IsBroken,
        If Not ref.IsBroken Then
            On Error Resume Next
            Debug.Print ref.FullPath,
            If Err.Number <> 0 Then
                Debug.Print "Error (FullPath)",
                Err.Clear
            End If
            On Error GoTo 0
        Else
            Debug.Print "N/A",
        End If
        Debug.Print
    Next ref
End Sub

comctl32.ocx: maybe this is no longer installed on the PC. The file belongs to VB6, doesn't it?