Identifying Broken VBA References

[Stewart Berman]

Given the following scenario:

There is a reference to in the VBA project in an MS Access database to a dll that not only isn't
registered but it doesn't exist on the computer. The Guid for the dll does not exist in the
registry.

If you open the VBA project (Alt-F11) and select Tools\References you will see the reference
identified as for example:

MISSING: Orbix COMet Types

And if you select that row you will see:

Location: C:\program files\Orbix.301\COMet\Bin\ITStdObjs.DLL

Which is were it was on the computer that the application was originally built on.

My question is how can you get that information from the application database?

If you try and walk the References collection the system will throw an error if you try to access
the FullPath property of the broken reference:

Error[-2147319779]: Method 'FullPath' of object 'Reference' failed

Where does the VBA IDE get the path information from? The Guid property is available but in this
case the Guid does not exist in the registry so it doesn't get it from the registry.

 

[Allen Browne]

Michael Kaplan has some info here on how to guarantee that references will
work in your applications
http://www.trigeminal.com/usenet/usenet026.asp?1033

He addresses the fact that you must identify what's broken in your code
*before* VBA realizes it has a missing reference, or else you're shot.

 

[Stewart Berman]

Michael Kaplan has some info here on how to guarantee that references will
work in your applications
http://www.trigeminal.com/usenet/usenet026.asp?1033

He addresses the fact that you must identify what's broken in your code
*before* VBA realizes it has a missing reference, or else you're shot.

I understand that. However the code that is walking the references collection is not in the
database being analyzed but in a separate database.

The analysis database creates a separate instance of Access and opens the target database while
suppressing any autoexec code using Dev Ashish's fGetRefNoAutoexec function. The only code running
is in the analysis database not the target and it has all valid references.

 

[Allen Browne]

Stewart, I have not done this, but is it possible to open another instance
of MSACCESS, and examine the Access.References in that instance?

Others who've experimented with it may have a better suggestion.

 

[Stewart Berman]

Michael Kaplan has some info here on how to guarantee that references will
work in your applications
http://www.trigeminal.com/usenet/usenet026.asp?1033

He addresses the fact that you must identify what's broken in your code
*before* VBA realizes it has a missing reference, or else you're shot.

Just to confirm that you can't get the name or fullpath of a broken reference I put this code:

Public Function CheckReferences()

Dim ref As Reference

Dim sref As String

For Each ref In Application.References
If (ref.IsBroken) Then
sref = ref.Name
sref = ref.FullPath
End If
Next ref

End Function

in a separate module in the target database -- the one with the missing references -- and created an
AutoExec macro that had one row -- RunCode specifying the CheckReferences function.

I then saved the module and macro and closed the database. The first time I opened it it threw an
error saying the Name method failed. I then commented out that line, saved the module and closed
the database. The next time I opened it it threw an error saying the FullPath method failed.

Finally, I deleted all objects (queries, forms, module, macros, etc.) except for the test module and
the AutoExec macros. I removed all references except the required ones and one bad reference. I
then compacted and repair the database. When it opened again it threw the same error.

So the question still is -- how do I get the path and file name of missing references?

 

[Graham Mandeno]

Hi Stewart

I'm not sure this will work, but try using complete disambiguation in your
CheckReferences function:

Dim ref As Access.Application.Reference

and

For Each ref In Access.Application.References

Also, you will presumably want to display the broken references, with MsgBox
or something, so use:

VBA.MsgBox

Remember that EVERYTHING must have a completely unambiguous definition or
reference.

 

[Douglas J. Steele]

Since you know what references are in your application when you build it,
you can include a table in your front-end that has the details of each
reference. You then compare your References collection to that table.

 

[david]

However, if you run this vbscript code against a database
with broken references, you can get fullpath. Or at least
you could, last time I tried:

' Check Reference
const sFile = "c:\my documents\db1.mdb"

dim oAccess
dim oRef
set oAccess = wscript.getobject(sfile)

for each oref in oAccess.References
wscript.echo oref.name & " Is broken? " & oref.isbroken
wscript.echo oref.FullPath
next

wScript.Quit( 0 )

Dunno what you'd get as full path on a broken reference:
don't remember, and don't have a note. Particularly good
for an MDE, where you can't open it up and examin it for
broken references.

(david)

 

[david]

Had another look. Found this:

"Historically the Fullpath and Name tests
have failed in a situation where isBroken did not fire."

That is, sometimes the FullPath was broken even though
IsBroken is false.

Also, note, you used to be able to test in a macro in a broken
mde, as long as you did it before running any VBA code:

.... [application].[References].[Item](3).[IsBroken]
.... [application].[References].[Item](4).[IsBroken]


(david)

 

[GeoffG]

Hi Doug/All,

Since you know what references are in your application
when you build it...

This seems to be the reason why the developer doesn't need
to obtain the full path from a reference object. However:

...you can include a table in your front-end that has the
details of each reference.

Is this wise? Doesn't this mean the developer has to use a
reference, say to DAO, to obtain the data from the table and
might not that reference be broken? It would seem safer -
if you need them - to hard-code a reference's name and path
into constants in the module doing the checking.

This is a thorny subject - not least because it seems
difficult for the developer to test code on the development
machine, where the reference isn't broken. Is there a way
to safely simulate a broken reference, I wonder?

It was news to me when Stewart said that the Name property
generated a run-time error when the reference was broken.
In notes I'd made to myself years ago, when testing whether
a library database was available, I found that, when the
reference to the library was broken, the Name property
contained the full path; and (obviously) when the reference
was not broken, it contained the name. I therefore used the
Name property to determine whether the library was
available. It seems I have wrongly assumed since then that
the Name property would supply the full path if the
reference is broken. And I have also wrongly assumed that
the same would apply to any broken reference. I shall
double-check my experience with library databases.

Access help gives the following code snippet:

Sub ReferenceProperties()
Dim ref As Reference

' Enumerate through References collection.
For Each ref In References
' Check IsBroken property.
If ref.IsBroken = False Then
Debug.Print "Name: ", ref.Name
Debug.Print "FullPath: ", ref.FullPath
Debug.Print "Version: ", ref.Major & "." &
ref.Minor
Else
Debug.Print "GUIDs of broken references:"
Debug.Print ref.GUID
EndIf
Next ref
End Sub

The above code avoids using the Name, FullPath, Major and
Minor properties when the reference is broken. But, as
Stewart says, it seems Access stores the FullPath somewhere
when the reference is broken. If the full path is not
stored in the FullPath or Name property, then where is it
stored?

The subject of references seems further complicated by the
seemingly unusual behaviour of the references object and
references collection. You have to loop through the
references collection to obtain a reference (broken or not
broken); you can't write can't get a reference when the
reference is broken by writing:

Set objREF = References(strRefName)

This will generate a "Subscript out of range error" (if the
reference is broken). That was my experience with library
databases.


Geoff

 

[Tony Toews [MVP]]

There is a reference to in the VBA project in an MS Access database to a dll that not only isn't
registered but it doesn't exist on the computer. The Guid for the dll does not exist in the
registry.

My question is how can you get that information from the application database?

Try some variation of the following:

Add a reference to "MS VBA Extensibility 5.3"

Then you can use the description property:

Dim ref As VBIDE.Reference

For Each ref In Access.Application.VBE.ActiveVBProject.References
Debug.Print ref.Description
Next

Tony

 

[Mark Han[MSFT]]

Hi Stewart,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that you fail to use VBA to involve a
access database reference. If I have misunderstood, please let me know.

after viewing the post history, I found that Allen Browne and Tony Toews
gave you an answer and detail example. Thank you guys. if their suggestions
does not work for you, in order to better assist you with the issue, please
post the detail error message and the code you use here.

Besides, I do some research for you, there is a article about similar issue
to share with you: http://support.microsoft.com/kb/208218

If there is anything unclear, please do not hesitate to let me know. I look
forward to your feedback.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Jack Leach]

Have you considered using the GUID of the reference to verify all of them
exist? I'm hardly an expert at this stuff but seem to remember from reading
somewhere that a GUID is an easier way to check your references.

For my case, this works great. I've got a checkref file loading into the FE
root directory that contains a listing of all the required GUID's, and on
startup in the autoexec (per Michael Kaplan's suggestion) all of the
references are checked.

True that using the File I/O does rely on a reference (such as pulling it
from a table would), but there's no code that doesn't, so you'd never get
away from that.

So the app opens, first thing it does in the best segregated module I could
find is opens a file that lists the GUID that the designer requires, verifies
that there are all there and that there is no extra, and then calls the
startup function.

I have tested this with not enough references and too many references, and
never ran into any trouble trying to find of if the reference was broken or
not (in fact I don't seem to need to check that?).

My apps have commented out pfRemoveReference(sGUID) and
pfAddReference(sGUID); I never got to finishing them as all of my clients are
in the same building as me and use only slightly different versions of the
fe, if at all.

In any case, through all this conversation I haven't heard anything about
checking for the GUID, which seemed to work good for me, and passed my trial
tests with oversufficient and insufficient references.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[GeoffG]

Thanks, Jack.
GUIDs needed a mention in this thread.

Have you considered using the GUID of the
reference to verify all of them exist?

My previous post told of my experience using a reference to
a project in another Access database. As you may know, in
this case, there is no GUID. GUID's only exist for
references to type libraries and executables.

The Kind property helps. The Kind property returns 0 when
the reference is to a type library or executable (when a
GUID is available) and returns 1 when the reference is to a
project (when a GUID isn't available).

This means, in VBA, you can check whether the Kind property
is True or False and then not use, or use, the GUID property
as appropriate.

True that using the File I/O does rely on a
reference (such as pulling it from a table
would), but there's no code that doesn't,
so you'd never get away from that.

Access has two built-in references; they are:

Visual Basic for Applications
Microsoft Access [Version Number] Object Library

These are always present and can be used with disambiguated
calls. In the module checking references, it seems safer
only to call primitive features - features that can be
guaranteed to exist in early versions of VBA and Access.
For example, I don't use the Split() function. Also, I'm
not sure when the InStr() and InStrRev() functions were
introduced, so I avoid them (in favor of byte arrays). I
don't call into any other library as it could be broken on
the production machine. I don't know whether all this is
strictly necessary but, in this special module, isn't it
better to be safe than sorry?


Geoff






"Jack Leach" <dymondjack at hot mail dot com> wrote in
message

 

[Jack Leach]

As you may know, in

this case, there is no GUID. GUID's only exist for
references to type libraries and executables.

I was not aware of this. So much for being of any help as far checking
references in another project. In fact, I have absolutely no experience
whatsoever in working with any project other than the open one.

This means, in VBA, you can check whether the Kind property
is True or False and then not use, or use, the GUID property
as appropriate.

I vaguely recall looking at this when I set up my refcheck procedures.
Apparently I didn't see a need to use it, but it's always nice to keep tucked
away in case the need arises.

I don't know whether all this is
strictly necessary but, in this special module, isn't it
better to be safe than sorry?

I certainly agree. Cover everything.


But really, none of this helps with the problem of verifying references in a
remote project. But if we know exactly how to do this within the current
project, perhaps there's a long way around? For whatever reason running the
code from the current db to check the references in the remote db seems to be
failing, so what about running code in the current db that in turn runs code
from the seperate db, which then checks the references (from inside the
remote db). Would this be possible? I think it's possible to run a public
procedure from a different db. If in fact we can do this, returning a value
from the seperate db may be an issue, but as a worst case you could always
write the findings to a text file and read it from the original project.

I'm not exactly sure how "opened" a db really is when accessed this way
(again, never done this), but maybe its a lead of some sort?

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thanks, Jack.
GUIDs needed a mention in this thread.

Have you considered using the GUID of the
reference to verify all of them exist?

My previous post told of my experience using a reference to
a project in another Access database. As you may know, in
this case, there is no GUID. GUID's only exist for
references to type libraries and executables.

The Kind property helps. The Kind property returns 0 when
the reference is to a type library or executable (when a
GUID is available) and returns 1 when the reference is to a
project (when a GUID isn't available).

This means, in VBA, you can check whether the Kind property
is True or False and then not use, or use, the GUID property
as appropriate.

True that using the File I/O does rely on a
reference (such as pulling it from a table
would), but there's no code that doesn't,
so you'd never get away from that.

Access has two built-in references; they are:

Visual Basic for Applications
Microsoft Access [Version Number] Object Library

These are always present and can be used with disambiguated
calls. In the module checking references, it seems safer
only to call primitive features - features that can be
guaranteed to exist in early versions of VBA and Access.
For example, I don't use the Split() function. Also, I'm
not sure when the InStr() and InStrRev() functions were
introduced, so I avoid them (in favor of byte arrays). I
don't call into any other library as it could be broken on
the production machine. I don't know whether all this is
strictly necessary but, in this special module, isn't it
better to be safe than sorry?


Geoff






"Jack Leach" <dymondjack at hot mail dot com> wrote in
message

Have you considered using the GUID of the reference to
verify all of them
exist? I'm hardly an expert at this stuff but seem to
remember from reading
somewhere that a GUID is an easier way to check your
references.

For my case, this works great. I've got a checkref file
loading into the FE
root directory that contains a listing of all the required
GUID's, and on
startup in the autoexec (per Michael Kaplan's suggestion)
all of the
references are checked.

True that using the File I/O does rely on a reference
(such as pulling it
from a table would), but there's no code that doesn't, so
you'd never get
away from that.

So the app opens, first thing it does in the best
segregated module I could
find is opens a file that lists the GUID that the designer
requires, verifies
that there are all there and that there is no extra, and
then calls the
startup function.

I have tested this with not enough references and too many
references, and
never ran into any trouble trying to find of if the
reference was broken or
not (in fact I don't seem to need to check that?).

My apps have commented out pfRemoveReference(sGUID) and
pfAddReference(sGUID); I never got to finishing them as
all of my clients are
in the same building as me and use only slightly different
versions of the
fe, if at all.

In any case, through all this conversation I haven't heard
anything about
checking for the GUID, which seemed to work good for me,
and passed my trial
tests with oversufficient and insufficient references.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't
work."
-Thomas Edison (1847-1931)

 

[Stewart Berman]

If you look at the message chain you will see that no one has described how to get the fullpath for
a broken reference. It is available in the MS Access VB IDE but there does not appear to be anyway
to get the information programmatically.

I have sent your an MS Access database that contains an AutoExec macro that executes the only
function in the only module in the database. It breaks trying to reference the FullPath property of
a broken reference. If you have code that gets around this please forward it to me or post it in
the microsoft.public.access.modulesdaovba news group.

 

[Stewart Berman]

Try some variation of the following:

Add a reference to "MS VBA Extensibility 5.3"

Then you can use the description property:

Dim ref As VBIDE.Reference

For Each ref In Access.Application.VBE.ActiveVBProject.References
Debug.Print ref.Description
Next

Tony

I have attached an MS Access database that contains an AutoExec macro that executes the only
function in the only module in the database. It breaks trying to reference the FullPath property of
a broken reference. If you have code that gets around this please forward it to me or post it in
the microsoft.public.access.modulesdaovba news group.

 

[david]

Well, you wouldn't use a Function in a Module for that.

You would use a Line in a Macro,

Open the autoexec macro.

From the main menu, select View, Condition

In the condition column put
[Application].[References].[Item](3).[IsBroken]

in the action column put
Msgbox

in the action properties put the Message property
=[Application].[References].[Item](3).[fullpath]


.... son-of-gun. Who knew that the Microsoft Web Application Stress Tool
installed and registered it's own copy of DAO350.dll in the (wrong) folder?

FWIW, I just removed that DLL. The application is now broken.
The macro works correctly, and still reports the full path as

C:\PROGRA~1\MICROS~1\MICROS~2\dao350.dll

even though that file no longer exists.

(david)

 

[GeoffG]

Stewart:

1.
Take a look at:
http://msdn.microsoft.com/en-us/library/aa163639(office.10).aspx

2.
Here's a variation of Tony Toews code:

Private Sub UseExtensibility()

' Requires a reference to:
' Microsoft Visual Basic for Applications
' Extensibility 5.3
'
' Stewart:
' See if reading the FullPath and Name properties
' of a VBIDE Reference object generate a run-time
' error for a broken reference.

Dim objREF As VBIDE.Reference

For Each objREF In
Access.Application.VBE.ActiveVBProject.References
If objREF.IsBroken Then
Debug.Print "Missing Reference:"
Else
Debug.Print "Reference (Not Broken):"
' The Description property generates
' a run-time error if a VBIDE Reference
' object is broken, so the following
' code line is only included here for
' unbroken references.
Debug.Print " Description: " &
objREF.Description
End If
Debug.Print " Name: " & objREF.Name
Debug.Print " FullPath: " & objREF.FullPath
Debug.Print " Type: " & objREF.Type
Debug.Print " BuiltIn " & objREF.BuiltIn
Debug.Print " IsBroken: " & objREF.IsBroken
Debug.Print " GUID: " & objREF.Guid
Debug.Print " Major: " & objREF.Major
Debug.Print " Minor: " & objREF.Minor
Debug.Print
Next

Set objREF = Nothing

End Sub

In fact, I'm not sure it's a good idea to use Extensibility
for your purposes. It seems the reference to Extensibility
could also be broken on the production machine, as
extensibility is not a required (built-in) reference.

3.
I wonder if it'd be best not use the examination database
you mention. In the database itself, could you not
programmatically quit the database if any references are
broken, showing an appropriate message to the user?

4.
What do you plan to do with the FullPath for a broken
reference?
The file isn't on the production machine anyway.

5.
A number of help pages on the Microsoft website are wrong.
All the following web pages say that a run-time error is
generated when reading the FullPath property of a broken
reference.

Access 2000:
http://msdn.microsoft.com/en-us/library/aa206032(office.10).aspx

Access 2002 (XP):
http://msdn.microsoft.com/en-us/library/aa159941(office.10).aspx

Access 2003:
http://msdn.microsoft.com/en-us/library/aa195908(office.11).aspx

As mentioned already in this thread, a run-time error is not
generated when reading the FullPath property of a broken
reference to a project in another Access database.
Additionally, the FullPath is duplicated in the Name
property. As a consequence, the syntax:

Set objREF = References("ReferenceName")

generates a run-time error, despite the reference still
being in the references collection. It seems you have to
loop through the references collection to instantiate a
reference variable to avoid this possible run-time error.
This behaviour is not mentioned on this Microsoft web page:

Reference Object (Access 2003 VBA Language Reference):
http://msdn.microsoft.com/en-us/library/aa223135(office.11).aspx

6.
The zip file you posted was unreadable.


Geoff

 

[Stewart Berman]

You used a GUID that existed. Now try one that doesn't.

I have attached a word document that containing the following:

1. A list of the references in the MDB VB project.
The fifth one is broken -- the DLL has never been registered and the Guid does not exist.

2. A Macro that consists of:
Begin
Condition ="[Application].[References].[Item](1).[IsBroken]"
Action ="MsgBox"
Argument ="=\"1\" & [Application].[References].[Item](1).[fullpath]"
Argument ="-1"
Argument ="0"
End
for references 1 through 6.

The results when it tries to process reference 5:
"Type mismatch"

The Action Failed message showing the condition as true. Unfortunately, the fullpath reference for
the fifth reference throws an error.

The Reply-To address in my message header is valid. If you send me an email with a valid return
address I would be happy to email you the MDB with the bad reference and the AutoExec macro. It
also includes my original AutoExec macro (renamed to xAutoExec and the module containing the code.

Well, you wouldn't use a Function in a Module for that.

You would use a Line in a Macro,

Open the autoexec macro.

From the main menu, select View, Condition

In the condition column put
[Application].[References].[Item](3).[IsBroken]

in the action column put
Msgbox

in the action properties put the Message property
=[Application].[References].[Item](3).[fullpath]


... son-of-gun. Who knew that the Microsoft Web Application Stress Tool
installed and registered it's own copy of DAO350.dll in the (wrong) folder?

FWIW, I just removed that DLL. The application is now broken.
The macro works correctly, and still reports the full path as

C:\PROGRA~1\MICROS~1\MICROS~2\dao350.dll

even though that file no longer exists.

(david)

I have attached an MS Access database that contains an AutoExec macro that
executes the only
function in the only module in the database. It breaks trying to
reference the FullPath property of
a broken reference. If you have code that gets around this please forward
it to me or post it in
the microsoft.public.access.modulesdaovba news group.