Programmatically Viewing/Adding References in Access

When your Access application is distributed to end users, they may have different versions of COM components. You can programmatically add references to resolve broken references due to differing versions. Here is how to get a list of all the current references in MS Access using VBA:

Public Sub PrintOutCurrentReferences()
    Dim iIndex As Integer
    
    For iIndex = 1 To application.References.Count
        Debug.Print application.References(iIndex).name  _
        & ", " & application.References(iIndex).GUID  _
        & ", " & application.References(iIndex).Major _
        & ", " & application.References(iIndex).Minor
    Next
End Sub

Example of adding a reference going from the latest version to the oldest version of the ADO Extension Library:

    'Add Microsoft ADO Ext for 2007, 2003, 2000
    If (AddReference("ADOX", "{00000600-0000-0010-8000-00AA006D2EA4}", 6, 0) = False) Then
        If (AddReference("ADOX", "{00000600-0000-0010-8000-00AA006D2EA4}", 2, 8) = False) Then
            Call AddReference("ADOX", "{00000600-0000-0010-8000-00AA006D2EA4}", 2, 5)
        End If
    End If

Below is the code to perform the action:

Private Function AddReference(sReferenceName As String, sReferenceGUID As String, iMajorVersion As Integer, iMinorVersion As Integer) As Boolean
    Dim bFound As Boolean
    Dim iIndex As Integer
        
    bFound = False
    'Try to find an existing reference
    For iIndex = 1 To application.References.Count
        If application.References(iIndex).name = sReferenceName Then
            bFound = True
            'Remove the reference if it is broken
            If application.References(iIndex).IsBroken Then
                application.References.Remove application.References(iIndex)
                bFound = False
            End If
            Exit For
        End If
    Next
        
   
    'If the reference was not found, or it was broken, add it
    If bFound = False Then
        On Error Resume Next
        application.References.AddFromGuid sReferenceGUID, iMajorVersion, iMinorVersion
        If Err.Number = 0 Then
            bFound = True
        Else
            Err.Clear
        End If
    End If
    
    AddReference = bFound

End Function