[AccessD] Removing a missing reference

Joe Hecht jmhecht at earthlink.net
Tue Aug 8 14:03:40 CDT 2006


Wow is this stuff way past me.

Good luck Rocky 


Joe Hecht
jmhecht at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, August 08, 2006 12:01 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Removing a missing reference

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list