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