Code to fix broken references cannot get reference properties (such as FullPath): Error -2147319779

When I installed an Access 2003 database on another computer, I had 2 broken references on the other computer. I can easily fix the references manually, but I need to do it at runtime because I will be installing the database--and the runtime version of Access--on several computers that either do not have Access or have an earlier version of Access (i.e., 97 or 2000).

I did find code that detects and fixes broken references at However, Access cannot get the fullPath (or any other properties) of my broken references--which means it cannot fix them. When I click on the misiisng references in the References list, Access displays the pathnames of these DLL files. I cannot figure out why Access cannot get the properties of these missing references. Any ideas?

Here is my code and the resulting debug messages form hte debug.print statements I added.

--------------------  CODE ---------------
Sub FixUpRefs()

'See if any references (to program code libraries) are broken.
'If a reference is broken, remove it and add it again. This
'will fix the problem that occurs when the the computer that created
'the program and the computer the program was installed upon have
'different versions of a code library.

On Error Resume Next

Dim loRef As Access.Reference
Dim intCount As Integer
Dim intX As Integer
Dim fBroke As Boolean
Dim strPath As String

'Count the number of references in the database
intCount = Access.References.Count

'Loop through all of the references.
'For each reference:
'   - see if it is broken
'   - if the reference is broken, then
'       - remove it and
'       - add it back in
For intX = intCount To 1 Step -1

    Set loRef = Access.References(intX)
    With loRef
        Debug.Print "Reference(" & intX & "):" _
            & "FullPath = " & .FullPath & vbCrLf _
            & "     Name = " & .Name & ", IsBroken = " & .IsBroken
        fBroke = .IsBroken
        If fBroke = True Or Err <> 0 Then
            Debug.Print intX & " is broken, Error " & Err.Number & ": " & Err.Description
            strPath = .FullPath
            With Access.References
                .Remove loRef
                .AddFromFile strPath
            End With
        End If
    End With

Set loRef = Nothing

'Call a hidden SysCmd to automatically compile and save all modules
Call SysCmd(504, 16483)

End Sub

------------------------- DEBUG LOG ------------------
Reference(7):FullPath = C:\WINNT\system32\stdole2.tlb
     Name = stdole, IsBroken = False
Reference(6):FullPath = C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
     Name = Office, IsBroken = False
Reference(5):FullPath = C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
     Name = DAO, IsBroken = False
4 is broken, Error -2147319779: Method 'FullPath' of object 'Reference' failed
3 is broken, Error -2147319779: Method 'FullPath' of object 'Reference' failed
Reference(2):FullPath = C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
     Name = Access, IsBroken = False
Reference(1):FullPath = C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
     Name = VBA, IsBroken = False

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You get the error because the refence isn't available, I'd assume ... the IsBroken property of reference is a generic property and can mean many different things. From my understanding, this code was built to Refresh references, not replace them entirely, so I'd assume that the reference would have had to be existing at some point in order to remove it.

If the only references you have which are giving problems are ADO and ADOX, then your best course of action is to remake those references on your development machine and set them to the version your end user will have. Assuming you're not using any features specific to the new libraries (and most likely you're not, unless you're doing some oddball stuff with ADO), then resetting those references to older versions on your dev machine will allow the refs to "travel" with your deployment, and the end user machine can then successfully upsize the ref if needed.

You may find that ou have several different versions of ADO on your dev machine ... right now, I've got 2.0, 2.1, 2.5, 2.6, 2.7 and 2.8 installed on my dev machine, and I simply make sure that I've got the correct reference when I make my final deploy copy (I typically use 2.1, since the only thing I do is build connections and recordsets and I'm almost assured that any Windows with Windows 98 or better will run the app). Don't worry about the ADOX reference (and are you sure you need it?) - it won't reset to a different number when you remake the ADO reference, but it's dependent on the version of ADO you have set, so just leave it be.

A warning (and this from experience, and not a very pleasant memory):You would be well advised to not muck around with ADO or ADOX references on the end user's machine (like attempting to reset or reregister them). Resetting references to the wrong library, or trying to re-register parts of ADO will leave you with a mis-matched installation of ADO and MDAC, a lot of programs suddenly going belly up (since many, many Windows programs depend on ADO) and, perhaps, a very angry customer. If you need to update the end user's computer, supply them with the link to the MS website where they can download and upgrade their machine and let them do so ... you can deploy the ADO install (it's named mdac_typ.exe) but you'd need to deploy all the needed versions, which could really bloat your deployment package.

Finally, you would also be well advised to invest in a virtual machine package like vmWare or Virtual PC and install the various OS and Office/Access environments you'll need to support. With your current testing method, you can only be assured of your app working in 2 different environments - the one on your dev machine, and the one on your test machine. If you're going to require that all client machines have a minimum install of Win2000sp4 with Access 2003sp2, then you should be fine, but you've said that you'll have to support other versions of Access as well. It's nearly impossible to troubleshoot problems on an enduser machine when you can't duplicate their environment (again, this from experience).

I suppose you mean the FileDialog object is the only object you're currently using which isn't supported in 97 or 2000 ... there are a LOT of objects included with 2002-2003 which aren't included in 2000 or 97.
See this tip from on the addFromGuid method:

 Get the guid of a library reference from code.  Since the guid is unique for every object library, for example, the guid of "Microsoft DAO 3.6 Object Library" is always {00025E01-0000-0000-C000-000000000046}, and you can obtain the guid of any object library using the above code. You can then use the .AddFromGUID method to add a reference to the object library without knowing the file path.

Sub AddReference()

    Dim refItem As Reference

    Set refItem = Access.References.AddFromFile _
    ("D:\Program Files\Common Files\Microsoft _

End Sub

Typo above :you can obtain the guid of any object library using the <<<<above code>>>S/B the <<<<code below>>>>>>>.

See also this link on the Guid method from the MS KB:

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When you say broken references, do you mean that the libraries or files you need are NOT present on the workstation to which you deploy your application? Or are those references there, but are perhaps the wrong version? Access doesn't care where the libraries are stored, it only cares that the item is correctly registered on the machine.

What references are broken?

You'll have no end of troubles when deploying your 2003 app to machines with 97 or 2000 on them, unless you are extremely careful to NOT use any objects, properties, or methods which are not part of 97 or 2000. In many cases, when you need to deploy to this many versions, you'd maintain several versions (perhaps a 97 version, a 2000 version and a 2002/2003 version). There are many items available to 2002/2003 which are NOT avilable to 97 and 2000 (for example, the Printer object, the FileDialog object, etc etc).

In short: You must code to the "lowest common denominator", which in your case is Access 97 (unless you do the multiple copy thing). Also, you must deploy, with your application, the files and such needed to insure your app works. Also, in many cases, you cannot deploy a reference and instead must make sure that the target machine has the necessary minimum requirements. Access can always "upsize" a reference, but generally cannot downsize one. For example, if you have a reference to Excel 9.0 and the user has Excel 10.0, Access will happily handle that but it won't do so the other way around.

There are also some files which you cannot redistribute - in your ref listing, for example, you've got MSO.DLL ... this file is NOT redistributable, and you cannot include that with your package. MSO.DLL is included in the Office installation, of course, so you don't really need it (note: mso.dll was included in office 2002 and 2003; earlier versions used different files for this).

Finally, the code you're using only works on mdb files. If you compile your code to the mde format, the code you're using will fail since you cannot reset references in an mde file. And, since you should be shipping a compiled file you'll need to find another method to handle this. Othewise you run the risk of someone mucking around in your code, or having troubles with corruption and de-compilation and the resultant performance problems.

gordonwwaughAuthor Commented:
To answer your questions and clarify ...

The development machine has Access 2003 (and Access Developer Extensions). The client machines so far have Access 2003 or Access 2000. It's likely some client machines that will eventually be used have Access 97. Still others will likely not have Access at all.

I am currently doing my testing using a Win2000sp4 computer with Access 2003sp2. The Access 2000 machines have older versions of the two libraries with the broken references.

Just to complicate things, the Access 2003 development computer has newer versions of the missing libraries than the test computer with Access 2003. Although they have the same service packs, the development computer has newer versions because I also installed SQL Server Desktop on it.

Here is more information about the libraries with the missing references. Note that the filenames are identical on the development vs. test computer. Also note that the GUIDs are the same on the two computers for the ADOX library but different for the ADODB library:

Reference 3 Library: ADODB
Name = Microsoft ActiveX Data Objects 2.8 Library
Version on Development Computer: 2.8
Version on Test Computer: 2.5
GUID on Development Computer vs. Test Computer:  Very different
Filename on Development Computer:
Filename on Test Computer: msado15.dll

Reference 4 Library: ADOX
Name = Microsoft ADO Ext. 2.8 for DDL and Security
Version on Development Computer: 2.8
Version on Test Computer: 2.5
GUID on Development Computer vs. Test Computer: Identical
Filename on Development Computer: msadox.dll
FullPath on Test Computer: msadox.dll

Because the GUIDs differ for the ADODB library, I assume I cannot use the AddFromGUID method for it. Or maybe I could specify the version 2.5 GUID. It's really a moot point, however, because the code cannot even remove the missing references. I get an error:

   Error -2147319779 Object library not registered

I would rather not rewrite my program to run under Access 97 if possible. Because I use mostly ADO in my program code, I would have to rewrite all this using DAO. I think the FileDialog object is the only object unsupported in Access 2000--so I would not mind rewriting that code using the Windows API for the OpenFile Dialog box.

Currently, the databases are .mdb files. That's okay for now, but it would be nice to use .mde files instead (for the reasons you mentioned). So, for now, I can use code to try to fix the references. Later, however, it looks like I cannot do that (when I use .mde files). By the way, MSO.DLL is not a problem. Its link is not broken.

My key question is: Why does my code get an error when I try to remove a missing reference?

gordonwwaughAuthor Commented:
I very much appreciate the level of explanation you provide. It helps me to not only fix my current problem but it also improves my level of understanding which will help me in the future.

I'll try resetting the ADODB reference on my development machine to version 2.1 and see what happens.

I will ask my IT folks if they can get me Virtual PC or vmWare for testing purposes. I have been thinking about getting it for a while. It sounds like it would make life much easier.
gordonwwaughAuthor Commented:
I changed the ADODB library reference to version 2.1 on the development machine. That fixed the ADODB broken reference on the target machine. I still have a broken reference for ADOX. I was able to manually fix the ADOX reference on the target machine.

I tried copying version 2.5 of msadox.dll from the target machine to the development machine (after renaming it on the development machine). However, as soon as I copy it, Windows replaces it with version 2.8.

So, I still have the problem with the broken reference for ADOX.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't simply copy over the dll files for these types of things, they're protected by the operating system. Try setting your reference to ADO 2.5 and see if that works. If it doesn't work, then you'll need to distribute the correct mdac_typ.exe file with your program. Also, this is by far the best way to ensure that your app will work on any platform.

You can (and should) use an installer to do this. There are several free ones, including Inno Setup: I used this product before switching to Wise, however Inno can do most things a commercial installer can, including checking for a specific version of mdac. You could do this on install and, if the machine isn't up to requirements, disallow the install, or install mdac for them, or provide them with a msgbox link to the MS site.

BTW, Virtual PC is free, so I can't see your IT guys saying much about it. Of course, you still have to have the licenses for the OS and progs that you're going to install on the Virtual PCs, but your IT guys most likely have all that:
gordonwwaughAuthor Commented:
Setting the reference to ADO 2.5 did not fix the ADOX reference link. I decided to rewrite my ADOX code in DAO. The program works and the references work on at least one Win2000 computer. So, I'll consider the question answered. The answer, for me, was to (a) rewrite the ADOX code in DAO and (b) change the ADO version on my development computer to 2.1 (by the way, 2.5 worked as well--at least on the Win2000 computer I tested).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.