Custom references, the perpetual nightmare

First, I won't speak about the list of references provided by the system, neither OCX or DLLs (in work environment, you don't always have the luxury to register these), but about references on VBA projects embedded in office documents (wich you can't late bind).

Since browsing the references collection and trying to fix broken references isn't reliable, removing one work, adding a new one work, but refreshing a broken one fail (delete and reload from a valid path), how do you deal with them ?

My last solution is to take adventage of the references lookup process, the application will resolve references by looking:
- The absolute path provided by the references collection.
- The application directory.
- The Windows system directory.
So, I wrote a little MS Access database (more or less "a launcher") that will extract any documents the application will need and launch the application.

The launcher ensure that references will be always valid.
End users will need to always use the launcher

LVL 14
Fabrice LambertConsultingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
First, I won't speak about the list of references provided by the system, neither OCX or DLLs (in work environment, you don't always have the luxury to register these), but about references on VBA projects embedded in office documents (wich you can't late bind).
disagree with you here. the installer for the application should do all of this.

In your case your 'application' could simply be a .dll rather than an EXE and call the functions from Access VBA
Fabrice LambertConsultingAuthor Commented:
disagree with you here. the installer for the application should do all of this.
Alas, not without elevated privileges.

And by "application", I'm speaking about VBA application, something done with Access, Excel, Outlook, Word, PowerPoint ect ..
Gustav BrockCIOCommented:
Actually, you can check and reestablish references (if the exist, of course).
However, you can't let the code compile itself, thus you must call this from a (hidden) (splash) form or from an AutoExec macro:

Option Compare Database
Option Explicit

Public Function VerifyReferences(ByVal booErrorDisplay As Boolean) As Boolean

' Verify Access' external references and re-establish these if possible.
' Uses function IsBroken97().
' 2001-07-29. Cactus Data ApS, CPH.
' 2003-07-23.
'   Changes:
'   Added call to DoCmd.Echo True to allow for display of MsgBox.
'   Removed call to SysCmd() compilation which did not work.
  Dim refA                    As Access.Reference
  Dim refX                    As Access.Reference
  Dim strRefFullPath          As String
  Dim booNotBuiltInRefExists  As Boolean
  Dim booIsBroken             As Boolean
  Dim booRefIsMissing         As Boolean
  Dim strMsgTitle             As String
  Dim strMsgPrompt            As String
  Dim strMsgHeader            As String
  Dim strMsgFooter            As String
  Dim lngMsgStyle             As Long
  Dim strCrLf                 As String
  ' No special error handling.
  On Error Resume Next
  ' User oriented error message.
  strMsgTitle = "Missing support file"
  strMsgHeader = "One or more supporting files are missing:" & vbCrLf
  strMsgFooter = vbCrLf & vbCrLf & "Report this to IT support." & vbCrLf
  strMsgFooter = strMsgFooter & "Program execution cannot continue."
  lngMsgStyle = vbCritical + vbOKOnly
  ' Look for the first reference in the database other than
  ' the built in "Access" and "Visual Basic for Applications".
  For Each refA In Access.Application.References
    If refA.BuiltIn = False Then
      ' At least one not built in reference is in use.
      booNotBuiltInRefExists = True
      ' Check if the reference is not broken.
      If IsBroken97(refA) = False Then
        ' The first not missing not built in reference is found.
        Set refX = refA
        Exit For
      End If
    End If
  If booNotBuiltInRefExists = False Then
    ' Only built in references are in use.
    ' Nothing more to do.
    If refX Is Nothing Then
      ' All not built in references are missing.
      ' Don't remove missing references as there is no way to
      ' re-establish a reference if its identity is lost.
      ' Remove this not built in reference and add it back to
      ' force Access to revalidate all references.
      ' This may or may not rebuild links to missing references.
      With Access.Application.References
        strRefFullPath = refX.FullPath
        .Remove refX
        .AddFromFile strRefFullPath
      End With
      Set refX = Nothing
    End If
    ' Check references if any should be missing.
    ' If so, no attempt to read a reference is done as it most likely
    ' either is not installed or has been moved to an unknown directory.
    For Each refA In Access.Application.References
      booIsBroken = IsBroken97(refA)
      If booIsBroken = True Then
        ' Build list of missing files.
        strMsgPrompt = strMsgPrompt & vbCrLf & refA.FullPath
      End If
      booRefIsMissing = booRefIsMissing Or booIsBroken
    ' If any reference is broken, display error message if requested.
    If booRefIsMissing = True And booErrorDisplay = True Then
      strMsgPrompt = strMsgHeader & strMsgPrompt & strMsgFooter
      Access.DoCmd.Echo True
      VBA.MsgBox strMsgPrompt, lngMsgStyle, strMsgTitle
    End If
  End If
  Set refA = Nothing
  ' If References have been updated, the application is left decompiled.
  ' Run command in AutoExec macro to compile and save all modules.
  VerifyReferences = Not booRefIsMissing

End Function

Public Function IsBroken97(ByVal ref As Access.Reference) As Boolean

' Alternative method to check if a reference is broken
' as the IsBroken property cannot be used in Access97.
' 2000-03-19. Gustav Brock. Cactus Data ApS.

' Refer to this article at Microsoft Technet:
' Article ID: Q186720
' The information in this article applies to:
' Microsoft Access 97
' In Microsoft Access, IsBroken is a property of the References collection.
' The Microsoft Access Help topic on the Isbroken property states the following:
' The IsBroken property returns a Boolean value indicating whether a
' Reference object points to a valid reference in the Windows Registry.
' Although this statement is correct, to receive this Boolean value
' you must trap for errors that are generated by the broken reference.
' Also, the IsBroken property becomes True only when the file being referenced
' is deleted and the Microsoft Windows Recycle Bin is emptied.
' This article details the steps necessary to receive the Boolean value.

  Dim booRefOK As Boolean
  On Error GoTo Err_IsBroken97
  If Len(Dir(ref.FullPath, vbNormal)) > 0 Then
    booRefOK = Not ref.IsBroken
  End If

  IsBroken97 = Not booRefOK
  Exit Function

  ' Ignore non existing servers, drives, and paths.
  Resume Exit_IsBroken97
End Function

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Fabrice LambertConsultingAuthor Commented:
In fact, deleting a (broken) reference on an office document and adding it back (with a different folder path) give me an error.
Not blocking tho, as the user just need to close and re-open the application, but annoying.
Gustav BrockCIOCommented:
How (and why) do you reference an office document?

Fabrice LambertConsultingAuthor Commented:
I wrote an object library in an access database saved as accde, who's purpose is to import data from (virtually) any data source to (virtually) any data source.
Data can be validated by colomns or by rows, casted from one data type to another if necessary, and an error collection is populated whenever data violate validation rules.

There arn't many options to reference the library database (go to references ==> add ==> browse ect ...), it work fine on my local computer.
Annoyances raise when deploying applications (Access database or Excel Workbook) using the library reference, as users can install (or copy) their application wherever they want.

I must ensure the library database is correctly referenced.
Gustav BrockCIOCommented:
I see. Office documents to me are mostly Word or Excel files.

And yes, it is also the case with an Access add-in is, that if you change this, the code of the application is left uncompiled.
It shouldn't matter (in this regard) where users install your app, what matters is where your add-in is located - so why don't you install that in a common place like %LocalAppData%\Lambert\AddIns ?

Did you try the trick of compiling from the AutoExec macro?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertConsultingAuthor Commented:
That is also an option (according our client's policy don't interfer).
We already use an (approved by hierarchy) library in a common place like you described in c:\users\Public.
My library isn't approved yet, so our team leader see placing it next to the "approved library" with a cold eye.

As for add-ins, Microsft speak about wizard, builders or menu add-ins, none fit a library providing only logic objects  (unless I missed something).
Gustav BrockCIOCommented:
It's a library.

I have never used such as you cannot update them without a recompile (or a matching update) of the main application(s).

Fabrice LambertConsultingAuthor Commented:
Thanks for your input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.