Solved

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

Posted on 2006-11-25
9
4,052 Views
Last Modified: 2008-10-25
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
----------------------------------------





0
Comment
Question by:gordonwwaugh
  • 4
  • 3
  • 2
9 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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

0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
0
 
LVL 84
Comment Utility
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.

0
 

Author Comment

by:gordonwwaugh
Comment Utility
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?

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:gordonwwaugh
Comment Utility
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.
0
 

Author Comment

by:gordonwwaugh
Comment Utility
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.
0
 
LVL 84
Comment Utility
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
0
 

Author Comment

by:gordonwwaugh
Comment Utility
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).
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now