[AccessD] Check if reference is broken (was: Combo Box Query Fails)

Hi Max and Charlotte

That's right, though neither have I checked this for newer versions. Here is an example of such 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.
' 2003-05-20. VBA explicitely stated for functions.

' 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 VBA.Len(VBA.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


>>> cfoust at infostatsystems.com 26-03-2010 16:23 >>>
The problem is that IsBroken is unreliable unless they've fixed it in 2007.  Back in the archives somewhere is a long discussion around broken references, code to determine whether they exist (it has to run first and be in its own module) and a workaround for IsBroken.  I wrote code for this for my employer, but as I recall they never used it because the boss didn't understand it.

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Friday, March 26, 2010 6:49 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Combo Box Query Fails

This is the simplest code I can come up with which will check it.

for each reference in application.References : debug.Print reference.name,
reference.isbroken : next

Just a one-liner without using any other functions.  Type it into the IW and
run it.

Max