13

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Falcon
  • 3,150
  • 2
  • 24
  • 35
  • Use late binding for Word and Excel. Then your problem goes away. – HansUp Apr 08 '11 at 13:54
  • If you're getting the wrong Access library version, even the built-in functions stop working, so late binding doesn't help. But this only happens on machines with different versions installed at the same time. – dwo Apr 10 '11 at 07:56

4 Answers4

12

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
TheLaurens
  • 405
  • 1
  • 6
  • 14
3

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.

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
  • But won't Excel start if I create an Excel.Application object? – Falcon Apr 08 '11 at 17:07
  • 1
    What are you using Excel for? – David-W-Fenton Apr 09 '11 at 22:47
  • Falcon, yes, Excel will start once you create the object. But that's what happens when you start using the Excel objects which are referenced. – Tony Toews Apr 12 '11 at 01:03
  • Tony...am I correct in assuming if I use Late Binding, I would have to have routines written for each Office App I am automating, with different syntax for each one of those app versions. For example, the app I have is such an atypical use case for Access, as I use it to build a quick GUI that allows folks to automate ppt presentations, excel workbooks, outlook emails, xml data files, word documents, etc in the field. So I would have to write a replication of the process for Word 2000, Word 2003, Word 2007 per each job, etc ...in order to use late binding, and still retain functionality? – Justin Apr 17 '11 at 18:50
  • No, you don't need different syntax for each version. You write basically the same code you would using the reference. The only difference is that you don't use data types from the referenced outside library, and you have to manually instantiate the instance of the outside application. Otherwise, it's exactly the same code. Why don't you actually try following the instructions for using late binding and see how it works? – David-W-Fenton Apr 18 '11 at 01:56
  • Justin, to add to David's comments, no. It's been my experience that the automation that we do is generally relatively simple and if it works in Office 2000 it works in Office 2010. That isn't always true of course if you are using a feature that was introduced in a later version of Office. But then that's part of what we programmers have to concern ourselves with. – Tony Toews Apr 18 '11 at 06:16
2

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
Mark Mooibroek
  • 7,636
  • 3
  • 32
  • 53
1

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
dwo
  • 3,576
  • 2
  • 22
  • 39
  • 2
    In my apps, I entirely avoid this problem by never using references for anything but the default 3, Access, VBA and DAO. Late binding is really the way to go, in my opinion. – David-W-Fenton Apr 09 '11 at 22:48
  • 1
    Personally, I do both. During development I use the references to have type checking and see code completion, then I switch to late binding. – dwo Apr 10 '11 at 07:53
  • dwo, correct. My page on late binding mentions that technique along with sample code to make the switching even easier. – Tony Toews Apr 12 '11 at 01:04
  • 1
    I may set a reference just long enough to get the Intellisense, and then get rid of it. For automating Office apps, I no longer even do that -- instead, I just fire up the app in question, open its VBE and use the native Intellisense. – David-W-Fenton Apr 13 '11 at 01:19
  • so I am confused by this. How can this bit of code, or any of these examples help if you don't know the target system/box and where the olb file is stored? how can this find ref.fullpath if the error thrown is RT 48 and the DLL can't be loaded, then it cannot return the name/path/major or minor? – Justin Apr 17 '11 at 14:37
  • i think i am missing something here because the idea of programmactically checking/deleting & updating the references is exactly what I need I believe, but I can't see where this would help (the above)? The method is broken can't/won't return the major and minor of the versions, FullPath, or even the Name, because it can't find it. So what I am missing? – Justin Apr 17 '11 at 18:46
  • If you open a database on a machine which misses one or some of the references, Access removes them automatically. This snippet of codes just gets them back. It was supposed to help you on the way how to add references by code. – dwo Apr 17 '11 at 20:45
  • 2
    dwo, no Access does not remove missing references automatically. Instead you get bizarre, incomprehensible errors elsewhere in your code such as when using string functions like trim, left or mid. – Tony Toews Apr 18 '11 at 06:18