Link to home
Start Free TrialLog in
Avatar of gordonwwaugh
gordonwwaughFlag for United States of America

asked on

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 http://support.microsoft.com/kb/194374/en-us. 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

    Err.Clear
   
    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
Next

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





Avatar of puppydogbuddy
puppydogbuddy

Gordon,
See this tip from www.aadconsulting.com 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 _
    Shared\DAO\dao360.dll")

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:

               http://support.microsoft.com/kb/197916
Avatar of Scott McDaniel (EE MVE )
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.

Avatar of gordonwwaugh

ASKER

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?

ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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: http://www.jrsoftware.org/isinfo.php. 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:

http://www.microsoft.com/windows/virtualpc/default.mspx
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).