ms access - 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

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 OfficeOffice12EXCEL.exe") <> "" And Not refExists("excel") Then
    Access.References.AddFromFile ("C:Program Files (x86)Microsoft OfficeOffice12EXCEL.exe")
End If
If Dir("C:Program Files (x86)Microsoft OfficeOffice14EXCEL.exe") <> "" And Not refExists("excel") Then
    Access.References.AddFromFile ("C:Program Files (x86)Microsoft OfficeOffice14EXCEL.exe")
End If
If Dir("C:Program Files (x86)Microsoft OfficeOffice12EXCEL.exe") = "" And Dir("C:Program Files (x86)Microsoft OfficeOffice14EXCEL.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