Option Compare Database
Option Explicit
' Verify Access' external references.
' Returns True if all references are valid.
' Will run quietly, if called with parameter Quiet as True.
'
' Is intended to be called as the first command from the AutoExec macro
' followed by a call to a function - for example CompileAndSave - that will
' "compile and save all modules".
'
' 2018-07-10. Cactus Data ApS, CPH.
'
Public Function VerifyReferences( _
Optional ByVal Quiet As Boolean) _
As Boolean
' Settings for message box.
Const Title As String = "Missing Support Files"
Const Header As String = "One or more supporting files are missing:"
Const Footer As String = "Report this to IT support." & vbCrLf & "Program execution cannot continue."
Const Buttons As Long = VbMsgBoxStyle.vbCritical + VbMsgBoxStyle.vbOKOnly
Dim Reference As Access.Reference
Dim Item As Integer
Dim Guid As String
Dim Major As Long
Dim Minor As Long
Dim Prompt As String
Dim Broken As Boolean
Dim SecondRun As Boolean
Dim Result As Boolean
Do
' Loop (a second time) the references and build a list of those broken.
Broken = False
Prompt = ""
For Each Reference In Access.References
If Reference.BuiltIn Then
' Nothing to check.
ElseIf IsBrokenExt(Reference) Then
Broken = True
Prompt = Prompt & Reference.Guid & " - " & Reference.Name & vbCrLf
End If
Next
If SecondRun Then
' Only shuffle the references once.
Exit Do
ElseIf Not Broken Then
' All references have been verified.
Else
' Try to remove the last non-broken reference and add it back.
' This will shuffle the Reference collection and may or may not
' cause a broken reference to be added back.
Item = Access.References.Count
Do
Set Reference = Access.References.Item(Item)
If Not Reference.BuiltIn Then
If Not IsBrokenExt(Reference) Then
' Record the reference's identification before removal.
Guid = Reference.Guid
Major = Reference.Major
Minor = Reference.Minor
' Remove this reference.
Access.References.Remove Reference
' Add back the removed reference.
Access.References.AddFromGuid Guid, Major, Minor
Exit Do
End If
End If
Item = Item - 1
' Exit loop when a built-in reference is met.
' These are always the top ones.
Loop Until Reference.BuiltIn
SecondRun = True
End If
Loop Until Not Broken
Result = Not Broken
If Result = False And Quiet = False Then
Prompt = Header & vbCrLf & vbCrLf & Prompt & vbCrLf & Footer
VBA.MsgBox Prompt, Buttons, Title
End If
Set Reference = Nothing
VerifyReferences = Result
End Function
' Performs an extended check if a reference is broken, as the
' IsBroken property doesn't check for unregistered files. Thus,
' an unregistered reference may fail even if not marked MISSING.
'
' 2018-07-09. Gustav Brock. Cactus Data ApS.
'
Public Function IsBrokenExt( _
ByRef Reference As Access.Reference) _
As Boolean
Dim NotBroken As Boolean
On Error GoTo Err_IsBrokenExt
' If the reference is not registered, calling property FullPath will fail.
' Even if the file exists in the Virtual File System, GetAttr will find it.
If (VBA.GetAttr(Reference.FullPath) And vbDirectory) <> vbDirectory Then
' FullPath is valid.
NotBroken = Not Reference.IsBroken
End If
Exit_IsBrokenExt:
IsBrokenExt = Not NotBroken
Exit Function
Err_IsBrokenExt:
' Ignore non-existing servers, drives, and paths.
Resume Exit_IsBrokenExt
End Function
And you may get away with a reccompile if any references has been changed:' Compile and save all (other) modules.
' Is intended to be called from the AutoExec macro after having verified the references.
'
' After tampering with the references, the application may appear to be compiled, which it is not.
' This function will not fail - even if the application is compiled or appears to be - if it is
' kept on it own in a separate module.
'
' It is not a subfunction as only functions can be called from the AutoExec macro.
'
' 2018-07-06. Cactus Data ApS, CPH.
'
Public Function CompileAndSave()
' The command:
'
' Application.RunCommand acCmdCompileAndSaveAllModules
'
' can not be used, as no module is open when the AutoExec macro runs.
' Thus, use this undocumented SysCmd() call.
Call SysCmd(504, 16483)
End Function
The files are attached as well.- VBA Extensibility. I don't have any idea what this does and therefore the potential impact of removing it.
- ADO Recordset Library. Mostly I am opening / closing Databases / Recordsets. However I do have code that does relinking.
- Early Binding. I use Late Binding to import Excel Files as I have worked with EE Experts and determine that this is my best option.
Removing the references as a test has no impact. If there are problems, you simply reinstate the reference. As mentioned earlier, all you do is compile your code (to ensure it works BEFORE removing the reference), and the remove the reference and recompile. After the recompile, if you have no issues then you can be sure you don't need that reference, and you can remove it. All in all, you are waaaay ahead of the game if you remove any references you don't actually need. What are .bas files?
Q45221TeanIdName