[AccessD] Removing a missing reference

Rocky Smolin - Beach Access Software bchacc at san.rr.com
Tue Aug 8 14:59:45 CDT 2006


Gustav:

After running this code I find that the missing reference is still 
checked.  Any other ideas?

TIA


Rocky


Gustav Brock wrote:
> Hi Rocky
>
> That sounds a bit strange, also regarding the exe-name.
> Here's a function that may help you:
>
> <code>
>
> 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
>   Next
>   
>   If booNotBuiltInRefExists = False Then
>     ' Only built in references are in use.
>     ' Nothing more to do.
>   Else
>     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.
>     Else
>       ' 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
>     Next
>   
>     ' If any reference is broken, display error message if requested.
>     If booRefIsMissing = True And booErrorDisplay = True Then
>       strMsgPrompt = strMsgHeader & strMsgPrompt & strMsgFooter
>       Access.Application.DoCmd.Beep
>       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
>
> </code>
>
> - and the IsBroken97 function:
>
> <code>
>
> 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
> '
> ' SYMPTOMS
> ' 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
>
> Exit_IsBroken97:
>   IsBroken97 = Not booRefOK
>   Exit Function
>
> Err_IsBroken97:
>   ' Ignore non existing servers, drives, and paths.
>   Resume Exit_IsBroken97
>   
> End Function
>
> </code>
>
> /gustav
>
>   
>>>> bchacc at san.rr.com 08-08-2006 20:29:35 >>>
>>>>         
> Gustav:
>
> The code I was using did go backwards through the references but still 
> choked on the first one which was the missing reference.  Seems there's 
> nothing in the reference so MsgBox ref.Name fails as well as .Remove.
>
> Rocky
>
>
> Gustav Brock wrote:
>   
>> Hi Rocky
>>
>> Further to Charlotte's comments, I faintly recall you have to move backwards in the References collection:
>>
>> Public Function ReferencesClean() As Boolean
>>
>> ' Remove all missing references.
>> ' Return True if no reference was removed.
>> '
>> ' 2001-08-20. Cactus Data ApS, CPH.
>>
>>   Dim ref         As Reference
>>   Dim lngItem     As Long
>>   Dim booMissing  As Boolean
>>   
>>   With References
>>     For lngItem = .Count To 1 Step -1
>>       Set ref = .Item(lngItem)
>>       If ref.BuiltIn = True Then
>>         ' No need to check built in reference.
>>       ElseIf IsBroken97(ref) Then
>>         .Remove ref
>>         booMissing = True
>>       End If
>>     Next
>>   End With
>>   
>>   Set ref = Nothing
>>   
>>   ReferencesClean = Not booMissing
>>   
>> End Function
>>
>> Still, your code will be left non-compiled after this. A method to compile the code is described by Charlotte and me if you search the archives of July 2003 for:
>> "Broken References in Runtime AXP and A97"
>>
>> /gustav
>>
>>   
>>     
>>>>> bchacc at san.rr.com 08-08-2006 19:46:07 >>>
>>>>>         
>>>>>           
>> Dear List:
>>
>> Still struggling with this references thing.  It seem the easiest thing 
>> to do would be to remove the missing reference in the Open event of the 
>> opening form in the event the app is loaded onto a machine which doesn't 
>> have the library.  However, all the code that I've seen to remove 
>> missing references fail.  Code like:
>>
>> Dim refCurr As Reference
>>     For Each refCurr In References
>>         If refCurr.IsBroken Then
>>             References.Remove refCurr
>>         End If
>>     Next
>>
>> Seems straightforward but when it comes to the missing reference it 
>> errors out on the .Remove line because it doesn't have a name.  So.  How 
>> do you remove a missing reference when the application the reference is 
>> referring to doesn't exist?
>>
>> MTIA
>>
>> Rocky
>>     
>
>   

-- 
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com




More information about the AccessD mailing list