Experts Exchange connects you with the people and services you need so you can get back to work.
Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
I may not be able to check them on the client machine.
Open in new window
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.
' 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
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.
strRefFullPath = refX.FullPath
Set refX = Nothing
' 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
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
VBA.MsgBox strMsgPrompt, lngMsgStyle, strMsgTitle
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
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
IsBroken97 = Not booRefOK
' Ignore non existing servers, drives, and paths.
Most do not have Access installed but these do.
Gustav: I understand the Runtime is required but I will check.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment