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

I think we need to find a way WITHOUT using any functions otherwise it will
TILT (as per the machines in the arcade when you push too hard).!!

Max
Ps. Charlotte and I must have worked for the same boss at some stage. Mine
didn't understand my code either - strange...

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, March 26, 2010 3:38 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Check if reference is broken (was: Combo Box
QueryFails)

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



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