Thursday, July 31, 2008

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

2 comments:

Rem said...

Nice post...

Anonymous said...

You are awesome, while tons of other posts failed, your post worked and solved my problem. THANK YOU.