Identifying Broken VBA References

[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.

 

[Stewart Berman]

The point of the exercise is to be able to programmatically get identifying information for broken
references. The means either the name or the fullpath -- both of which are somewhere in the
database as the VB IDE can display them.

Stewart:

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

From the reference:
Note The Microsoft® Access Application object provides a References collection and Reference
object that make it possible for you to work with references in an Access VBA project without
requiring you to establish a reference to the Microsoft Visual Basic for Applications Extensibility
5.3 type library. For more information about the Access References collection, search the Microsoft
Access Visual Basic Reference Help index for "References collection."

This does not work.

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

I changed the code slightly to:

Dim objREF As VBIDE.Reference

On Error Resume Next

For Each objREF In Access.Application.VBE.ActiveVBProject.References
Debug.Print "Next Reference:"
If objREF.IsBroken Then
Debug.Print vbTab & "Missing Reference:"
Else
Debug.Print vbTab & "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 vbTab & " Description: " & objREF.description
End If
Debug.Print vbTab & " Name: " & objREF.Name
Debug.Print vbTab & " FullPath: " & objREF.FullPath
Debug.Print vbTab & " Type: " & objREF.Type
Debug.Print vbTab & " BuiltIn " & objREF.BuiltIn
Debug.Print vbTab & " IsBroken: " & objREF.IsBroken
Debug.Print vbTab & " GUID: " & objREF.Guid
Debug.Print vbTab & " Major: " & objREF.Major
Debug.Print vbTab & " Minor: " & objREF.Minor
Debug.Print
Next

Set objREF = Nothing

This produces (with the first three not broken references deleted):

Next Reference:
Reference (Not Broken):
Description: Microsoft Forms 2.0 Object Library
Name: MSForms
FullPath: C:\WINDOWS\system32\FM20.DLL
Type: 0
BuiltIn False
IsBroken: False
GUID: {0D452EE1-E08F-101A-852E-02608C4D0BB4}
Major: 2
Minor: 0

Next Reference:
Missing Reference:
Type: 0
BuiltIn False
IsBroken: True
GUID: {4E4AC072-D051-EB92-1D54-8DC5C371C9CD}
Major: 1
Minor: 0


You will notice that if the reference is broken the Name and FullPath also generates a run-time
error which is why they are not displayed..

The Reply-To header of my message has a valid email address. If you send me an email with a valid
return address I would be happy to send you the database to play with.

 

[Mark Han[MSFT]]

Hi Stewart,

Thank you for the update.

in order to address your concern, i would like to explain the following
1) If your application attempts to use a broken reference, an exception
error is generated. The inability to find the referenced component is the
primary trigger for the error, but there are several situations in which a
reference can be considered broken.

2) about your questions, Where does the VBA IDE get the path information of
a broken reference? based on my research, there is not a oficial article to
explain the process and the detail explanation for the process is out of
our services. personally, I would like to explain my understanding on the
process.
the process is that, when we first reference the object, in order to
improve the performance, the information of the object is saved in some
temp page; Assume that the object is renamed or deleted; but the
information saved in the temp page is not cleared. So the VBA IDE is also
get the path of the broken reference.

3 based on my reasrch and test, the issue is by design, we are not able to
get information of broken reference from the application database.

Besides, if you would like to send me the Access database, please via
(e-mail address removed) (please remove the online). my email is
(e-mail address removed) (please remove the online).

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.
=========================================================

 

[david]

OK, I'll take your word for it.

(david)

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)

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.

<snip>

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

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.

 

[Stewart Berman]

See inline response.

Hi Stewart,

Thank you for the update.

in order to address your concern, i would like to explain the following
1) If your application attempts to use a broken reference, an exception
error is generated. The inability to find the referenced component is the
primary trigger for the error, but there are several situations in which a
reference can be considered broken.

2) about your questions, Where does the VBA IDE get the path information of
a broken reference? based on my research, there is not a oficial article to
explain the process and the detail explanation for the process is out of
our services. personally, I would like to explain my understanding on the
process.
the process is that, when we first reference the object, in order to
improve the performance, the information of the object is saved in some
temp page; Assume that the object is renamed or deleted; but the
information saved in the temp page is not cleared. So the VBA IDE is also
get the path of the broken reference.

3 based on my reasrch and test, the issue is by design, we are not able to
get information of broken reference from the application database.

The information is in the MDB file. There are two ways to get it. The first (and approved way) is
to use one of the object collections. I take it from your response that the information is not
available via an exposed collection. That implies that the only way to get the information is to
hack the MDB database structure. Please confirm that Microsoft offers no alternative to hacking the
MDB database structure to be able to programmatically determine the name and fullpath of missing
references

 

[GeoffG]

Hi Stewart:

The point of the exercise is to be able
to programmatically get identifying
information for broken references.
The means either the name or the fullpath
-- both of which are somewhere in the
database as the VB IDE can display them.

OK, I understand what you're trying to do. I'm working on a
couple of solutions and I think they'll do the trick.

I suspect you need a different approach. Just because the
FullPath and Name are displayed in the VB IDE when a
reference is broken, doesn't mean you can necessarily get at
them through the built-in (or other) object models. It
seems Microsoft have chosen to expose or hide properties
depending on whether the reference is broken for reasons we
may not yet fully understand. However, this doesn't mean
that you can't achieve your objective.

I'll get back to you and Jack as soon as I can. I'm not
sure it's a good idea to use an external database to check
references, but I'll think about it.

By the way, your Word document attachment (listing all
references) won't open. (I don't know if you can
successfully post attachments to a newsgroup.) It might be
useful if you could list the references in the newsgroup,
perhaps using the code I posted earlier so I can see all
information about all the references. (Actually, I think I
won't need information about the references to Access or VBA
as these are built-in and cannot be removed. In any case,
I'm pretty sure Access will automatically change the
reference in your mdb file to the Access object library for
the version of Access used to load the mdb file.)


Geoff

 

[Stewart Berman]

Using notepad one finds:
C : \ P r o g r a m F i l e s \ C o m m o n F i l e s \ M i c r o s o f t S h a r e d \ V B A
\ V B A 6 \ V B E 6 . D L L # V i s u a l B a s i c F o r A p p l i c a t i o n s

* \ G { 4 A F F C 9 A 0 - 5 F 9 9 - 1 0 1 B - A F 4 E - 0 0 A A 0 0 3 F 0 F 0 7 } # 9 . 0 # 0 # C :
\ P r o g r a m F i l e s \ M i c r o s o f t O f f i c e \ O f f i c e \ m s a c c 9 . o l b #
M i c r o s o f t A c c e s s 9 . 0 O b j e c t L i b r a r y Z
* \ G { 0 0 0 2 E 1 5 7 - 0 0 0 0 - 0 0 0 0 - C 0 0 0 - 0 0 0 0 0 0 0 0 0 0 4 6 } # 5 . 3 # 0 # C :
\ P r o g r a m F i l e s \ C o m m o n F i l e s \ M i c r o s o f t S h a r e d \ V B A \ V
B A 6 \ V B E 6 E X T . O L B # M i c r o s o f t V i s u a l B a s i c f o r  žš



K 1
[
A p p l i c a t i o n s E x t e n s i b i l i t y 5 . 3 Þ * \ G { 0 D 4 5 2 E E 1 -
E 0 8 F - 1 0 1 A - 8 5 2 E - 0 2 6 0 8 C 4 D 0 B B 4 } # 2 . 0 # 0 # C : \ W I N D O W S \ s y s t
e m 3 2 \ F M 2 0 . D L L # M i c r o s o f t F o r m s 2 . 0 O b j e c t L i b r a r y
è * \ G { 4 E 4 A C 0 7 2 - D 0 5 1 - E B 9 2 - 1 D 5 4 - 8 D C 5 C 3 7 1 C 9 C D } # 1 . 0 # 0 # c
: \ p r o g r a m f i l e s \ O r b i x . 3 0 1 \ C O M e t \ B i n \ I T S t d O b j s . D L L #
O r b i x C O M e t T y p e s



The last valid reference is:
C : \ W I N D O W S \ s y s t e m 3 2 \ F M 2 0 . D L L # M i c r o s o f t F o r m s 2 . 0 O
b j e c t L i b r a r y

What I want to get at programatically is the missing reference:
c : \ p r o g r a m f i l e s \ O r b i x . 3 0 1 \ C O M e t \ B i n \ I T S t d O b j s . D L L
# O r b i x C O M e t T y p e s

A hacking we will go, a hacking we will go.....

 

[Stewart Berman]

By the way, your Word document attachment (listing all

references) won't open. (I don't know if you can

Interesting as I can combine the segments from the news group and open the document. It is possible
that the pictures in the document are represented by references to files on my machine. I am able
to open the document on another machine but it contains combined attachments.

Did you try combining the messages that make up the attachment before trying to open it?

The MDB file and the DOC file are in:
http://www.saberman.com/brokenreference.zip

Let me know how you make out.

 

[GeoffG]

Hi Stewart:

Quick response:
I don't think you need hack the mdb file.
You need a different programmatic approach.
I'll get back when done.

Geoff

 

[Tony Toews [MVP]]

A hacking we will go, a hacking we will go.....

See if Wayne at http://www.everythingaccess.com would create such a
utility.

Speaking of hacking: MDB Tools is a set of open source libraries and
utilities to facilitate exporting data from MS Access databases (mdb
files) without using the Microsoft DLLs. Thus non Windows OSs can read
the data. Or, to put it another way, they are reverse engineering the
layout of the MDB file.

http://sourceforge.net/projects/mdbtools/ Download the file and open
the HACKING file.

Tony

 

[Tony Toews [MVP]]

... 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?

WTF? Never mind that it exists, at least in my system, in "C:\Program
Files\Common Files\Microsoft Shared\DAO" And that is very likely an
excellent location for it.

Tony

 

[Stewart Berman]

Thanks.

I took a quick look through the .c and .h files in the download. It does not appear to cover
references which is to be expected as the project focuses on accessing the data and relationships in
the MDB file. I also noticed that the download is five years old and work on the project stop a few
years ago.

However, there maybe enough information to find the offsets to the information I want.
Unfortunately, there is a note in one of the header files that the offsets are release dependent and
the last version of Access referenced was 2002.

 

[Stewart Berman]

The MsysAccessObjects table does appear to have the information I want. Unfortunately, the data
column is defined as OLE and appears to have multiple sets of information in each record but it does
have the information sought.

 

[Mark Han[MSFT]]

Hi Stewart,

Thank you for the update and let us the current status of the issue.

if it is convenient t to you, could you please send a sample of the access
database file to me. i would like to reoproduce the issue inside and
discussing internal to try to sum up a method for you.

my email address is (e-mail address removed) (please remove "online")

I look forward to your update.

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.
=========================================================

 

[Stewart Berman]

Mark,

I have sent you the database with the broken reference as well as the word document breaking out the
details.

If you cannot access the attached document it is available at::
http://www.saberman.com/brokenreference.zip

 

[Stewart Berman]

Did you have a chance to look at the database I sent you?