Add references programmatically

Question

we have an Access-Application which does not work on some clients, mainly because references are broken. That happens for example when you start the access application with access runtime 2007 but have office in version 2003 or 2000 installed. Functions like Left/Right/Trim etc. just stop working then.

I think the only way to fix this problem is to programmtically check which office version is installed and add the references programmatically as in these heterogenous environments we cannot control what the user has installed. Specifically I need to reference the Microsoft Office Object libraries for Excel and Word.

But I neither have the guids of all office versions nor have a clue how to check them automatically.

Answer 1

If you ship an MDE/ACCDE you can't update your references.

But what specific references are causing you your problems? Chances are you are referencing Word, Excel or Outlook. If so use late binding so your solution doesn't matter what version is installed on the client system.

Late binding means you can safely remove the reference and only have an error when the app executes lines of code in question. Rather than erroring out while starting up the app and not allowing the users in the app at all. Or when hitting a mid, left or trim function call.

This also is very useful when you don't know what version of the external application will reside on the target system. Or if your organization is in the middle of moving from one version to another.

For more information including additional text and some detailed links see the "Late Binding in Microsoft Access" page.

Answer 2

So yeah, this answer is a bit late, but just in case someone stumbles across this like I did looking for an answer, I figured out the following bit of code to add an excel reference and it seems to work fine, also in MDE/ACCDE!

If Dir("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe") <> "" And Not refExists("excel") Then
    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe")
End If
If Dir("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") <> "" And Not refExists("excel") Then
    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe")
End If
If Dir("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe") = "" And Dir("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") = "" Then
    MsgBox ("ERROR: Excel not found")
End If

And the refExists references the following function:

Private Function refExists(naam As String)
Dim ref As Reference
refExists = False
For Each ref In References
    If ref.Name = naam Then
        refExists = True
    End If
Next
End Function

Answer 3

Here is an example - it check for certain references - deleting them and importing the Access 2000 variant. Just to make sure all clients use the same (lowest) version of the dependencies

Sub CheckReference()
' This refers to your VBA project.
    Dim chkRef As Reference ' A reference.

    Dim foundWord, foundExcel As Boolean

    foundWord = False
    foundExcel = False

    ' Check through the selected references in the References dialog box.
    For Each chkRef In References


        ' If the reference is broken, send the name to the Immediate Window.
        If chkRef.IsBroken Then
           Debug.Print chkRef.Name
        End If

        If InStr(UCase(chkRef.FullPath), UCase("MSWORD9.olb")) <> 0 Then
            foundWord = True
        End If

        If InStr(UCase(chkRef.FullPath), UCase("EXCEL9.OLB")) <> 0 Then
            foundExcel = True
        End If

        If InStr(UCase(chkRef.FullPath), UCase("MSWORD.olb")) <> 0 Then
            References.Remove chkRef
        ElseIf InStr(UCase(chkRef.FullPath), UCase("EXCEL.EXE")) <> 0 Then
            References.Remove chkRef
        End If


    Next

    If (foundWord = False) Then
        References.AddFromFile ("\\pathto\database\MSWORD9.OLB")
    End If

    If (foundExcel = False) Then
        References.AddFromFile ("\\pathto\database\EXCEL9.OLB")
    End If

End Sub

Answer 4

Here is a code sample, which checks for broken references. I know this is not the whole solution for you, but it will give you some clues how to do it.

Public Function CheckRefs()
    On Error GoTo Handler

    Dim rs As Recordset
    Dim ref As Reference
    Dim msg As String

    For Each ref In Application.References
        ' Check IsBroken property.
        If ref.IsBroken = True Then
            msg = msg & "Name: " & ref.Name & vbTab
            msg = msg & "FullPath: " & ref.FullPath & vbTab
            msg = msg & "Version: " & ref.Major & "." & ref.Minor & vbCrLf
        End If
    Next ref

    If Len(msg) > 0 Then MsgBox msg
    Exit Function

Handler:
    ' error codes 3075 and 3085 need special handling

    If Err.Number = 3075 Or Err.Number = 3085 Then
        Err.Clear
        FixUpRefs
    Else
        rs.Close
        Set rs = Nothing
    End If
End Function

Private Sub FixUpRefs()
    Dim r As Reference, r1 As Reference
    Dim s As String

    ' search the first ref which isn't Access or VBA
    For Each r In Application.References
        If r.Name <> "Access" And r.Name <> "VBA" Then
            Set r1 = r
            Exit For
        End If
    Next
    s = r1.FullPath

    ' remove the reference and add it again from file
    References.Remove r1
    References.AddFromFile s

    ' hidden syscmd to compile the db
    Call SysCmd(504, 16483)
End Sub