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

I seem to recall reading that if you prefix your VBA functions with VBA. then the will run even with broken references, and indeed this is illustrated in the quoted article 

"If VBA.Len(VBA.Dir(ref.FullPath, vbNormal)) > 0 Then"

so you *can* use functions, just not the usual way.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Friday, March 26, 2010 11:56 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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