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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart Berman" <sabe...@nospam.nospam> wrote in message
news:gn51255vd0159c27r...@4ax.com...

[Stewart Berman]

"Allen Browne" <Allen...@SeeSig.Invalid> wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart Berman" <sabe...@nospam.nospam> wrote in message

news:tnd3251fnvc5jhk68...@4ax.com...

[Stewart Berman]

"Allen Browne" <Allen...@SeeSig.Invalid> wrote:

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

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Stewart Berman" <sabe...@nospam.nospam> wrote in message

news:osi325d9js4log2vu...@4ax.com...

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Stewart Berman" <sabe...@nospam.nospam> wrote in message

news:osi325d9js4log2vu...@4ax.com...

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

"Stewart Berman" <sabe...@nospam.nospam> wrote in message

news:osi325d9js4log2vu...@4ax.com...

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

"david" <da...@nospam.au> wrote in message
news:eOLW9So4...@TK2MSFTNGP02.phx.gbl...

[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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote
in message news:e1334Fj4...@TK2MSFTNGP02.phx.gbl...

[Tony Toews [MVP]]

Stewart Berman <sabe...@nospam.nospam> wrote:

>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
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

[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: msd...@microsoft.com.
=========================================================
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
news:0BB31112-84B2-47EE...@microsoft.com...

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

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

"Tony Toews [MVP]" <tto...@telusplanet.net> wrote:

>Stewart Berman <sabe...@nospam.nospam> wrote:
>
>>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

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

"Stewart Berman" <sabe...@nospam.nospam> wrote in message

news:5ase255vvib5lbr0c...@4ax.com...

[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" <sabe...@nospam.nospam> wrote in message

news:u3qe25ha52j1d2dd3...@4ax.com...

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

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

"GeoffG" <geo...@nonospam.com> wrote:

>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
v-fa...@online.microsoft.com (please remove the online). my email is
v-fa...@online.microsoft.com (please remove the online).

[david]

OK, I'll take your word for it.

(david)

"Stewart Berman" <sabe...@nospam.nospam> wrote in message

news:irim259vfrod0nqdk...@4ax.com...

[Stewart Berman]

See inline response.

v-fa...@online.microsoft.com (Mark Han[MSFT]) wrote:

>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" <sabe...@nospam.nospam> wrote in message

news:pdkm25147e0ca3rad...@4ax.com...

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

v-fa...@online.microsoft.com (Mark Han[MSFT]) wrote:

[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

"Stewart Berman" <sabe...@nospam.nospam> wrote in message

news:n04s25t473c5bjrhp...@4ax.com...

> 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 z s

[Tony Toews [MVP]]

Stewart Berman <sabe...@nospam.nospam> wrote:

>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 Toews [MVP]]

"david" <da...@nospam.au> wrote:

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

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

"Tony Toews [MVP]" <tto...@telusplanet.net> wrote:

[AccessVandal via AccessMonster.com]

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

That’s right, you must unregistered the reference by using "regsvr32" with
the "/u" option and reboot the machine. Else the GUID will remain in the
registry or somewhere. Renaming or deleting the references would not remove
the GUID.

In my test with an unregistered ocx on a machine, the name and path will not
be available in the references but the GUID exist so is Major and Minor.

However, the information is available in the hidden tables called
"MsysAccessObjects" but the information there may be incorrect. I’m not
familiar with reading OLE object in Access, so can’t help here.

Stewart Berman wrote:
>You used a GUID that existed. Now try one that doesn't.
>
>I have attached a word document that containing the following:
>

snip....

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200906/1

[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 v-fa...@microsoft.online.com (please remove "online")

I look forward to your update.

[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

v-fa...@online.microsoft.com (Mark Han[MSFT]) wrote:

[Stewart Berman]

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

v-fa...@online.microsoft.com (Mark Han[MSFT]) wrote: