Can a missing reference in Access be cleared with Vba or is the dialog box the only way

Question

Hi Everyone,

I've been trying to figure out how to remove a "broken reference" which in fact is missing and replace it with the correct one.  Sceanario;

Access project written in 2007 and it uses Outlook to email files.  When installed on a machine with an earlier version of office all of the references for Access update appropriately.  However the Outlook reference breaks.  I get an error when I try to restore the reference with the correct outlook olb file.  Snipet of code below:

Set fsOutlookRef = CreateObject("Scripting.FileSystemObject")

' Enumerate through References collection.

    For Each ref In References

        If IsMissing(ref.Name = "Outlook") = True Then

            If ref.IsBroken = True Then

                iref = References.Count

                References.Remove iref

                Exit For

            Else

                strRefPath = ref.FullPath

            End If

        End If

    Next ref

    If IsEmpty(strRefPath) = True Then    If IsEmpty(strRefPath) = True Then

    'Check for Microsoft Office and Outlook default path create if does not exist

        If fsOutlookRef.FileExists("C:\Program Files\Microsoft Office\Office10\msoutl.olb") = True Then

            strRefPath = "C:\Program Files\Microsoft Office\Office10\msoutl.olb"

            References.AddFromFile strRefPath

        ElseIf fsOutlookRef.FileExists("C:\Program Files\Microsoft Office\Office11\msoutl.olb") = True Then

            strRefPath = "C:\Program Files\Microsoft Office\Office11\msoutl.olb"

            References.AddFromFile strRefPath

        ElseIf fsOutlookRef.FileExists("C:\Program Files\Microsoft Office\Office12\msoutl.olb") = True Then

            strRefPath = "C:\Program Files\Microsoft Office\Office12\msoutl.olb"

            References.AddFromFile strRefPath

        ElseIf fsOutlookRef.FileExists("C:\Program Files\Microsoft Office\Office14\msoutl.olb") = True Then

            strRefPath = "C:\Program Files\Microsoft Office\Office14\msoutl.olb"

            References.AddFromFile strRefPath

        Else

            MsgBox "A valid version of Outlook is missing or not installed."

        End If

    End If

All is good until the remove action and then it errors out with "Error loading DLL".

Is the Dialog box the only way to clear it?

I have a work-around and that is to remove the outlook reference before creating the install package and let this code find the version of outlook and add it that way.  I've tested it and it works.  But I would prefer to be able to hamdle broken references.

Thanks in advance for you time and consideration.

Manny

Answer 1

Missing references tend to make all code fail. In theory it is possible to write code in such a way that missing references can be fixed in code, but in practice it's not realistic.

A better option is to use late binding so that you don't need a reference to the Outlook library. See http://www.eileenslounge.com/viewtopic.php?f=29&t=8042.

Answer 2

I ended up finding a way (clumsily) to do this.  You just need to make sure you have a script at the closing of your database to drop the references that cause problem.  And then add them when opening.  I used this site as a reference. http://stackoverflow.com/questions/594239/how-to-add-remove-reference-programmatically

This has an obvious problem in that anyone who force quits will break the chain.  But, for my needs, it was good enough.

Also, the "names" are "outlook", "Word", etc., even though the library references are the full file path.

Answer 3

Thanks! Greatly appreciate the response and for taking the time.

Regards.