MS-Access / Getting Started

---

Updating References

References in your application provide library code that can be reused. Whether it's VBA code in another database that is being used as a library, a separate DLL, or ActiveX control, you may need a way to determine whether a reference is missing and subsequently update the reference. Missing references can cause expressions in the application to break.

To determine whether a reference is missing, check the IsBroken property of the Reference object, as shown in the following code. The Access Application object includes a property called BrokenReference that can be used as an optimization when iterating through references. This property returns True if there is a broken reference in the database. Check this property first to determine whether you need to iterate.

The following code shows you how to iterate through and update a reference to another Access databases. This code includes a call to the InstallFromAttachment method shown in the previous example.

Create a new standard module and add the following routine:

Public Function EnsureReferences() As Boolean
    Dim ref As Access.Reference
    Dim stFile As String
    Dim stPath As String
    Dim stName As String
    Dim objInstaller As Installer

Before we iterate through the references, let's check the BrokenReference property:

    ' if there are no missing references, return
    If (Not Application.BrokenReference) Then
	EnsureReferences = True
	Exit Function
    End If

If there are missing references, create a new instance of the Installer class.

    ' create the installer instance
    Set objInstaller = New Installer

Begin a loop to iterate through the References collection. We'll check the IsBroken property of the Reference object to determine whether we need to take an action.

    ' iterate through references
    For Each ref In Access.Application.References
	If (ref.IsBroken) Then
	    ' save the file information
	    stFile = VBA.Mid(ref.FullPath, VBA.InStrRev(ref.FullPath, "\") + 1)
	    stPath = Access.Application.CurrentProject.Path
	    stName = VBA.Left(stFile, VBA.InStr(stFile, ".") - 1)

After saving some information about the reference, remove it.

	    ' remove the reference
	    Access.Application.References.Remove ref

Our sample database contains a copy of the library database so we'll install it using the InstallFromAttachment code shown earlier. We'll create the library database in the next section.

	    ' install it from the attachment field
	    objInstaller.InstallFromAttachment stName, stPath

Next, we'll re-add the reference using the AddFromFile method and exit the routine.

	    ' re-add the reference
	    Access.Application.References.AddFromFile stPath & "\" & stFile
	End If
    Next
    ' compile
    If (Not Access.Application.IsCompiled) Then
	Access.Application.RunCommand acCmdCompileAllModules
    End If

    ' cleanup
    Set objInstaller = Nothing

    ' return
    EnsureReferences = True
End Function