Backwards Compatibility Issue - References

[bdbmeag]

Hi,

I'm developing a database (.mdb) file using Access 2007 in Vista. This database includes a reference to "Microsoft Excel 12.0 Object Library".

I need this database also to work when it is opened using Access 2003 in XP. In order for this to work I need to change the reference to "Microsoft Excel 11.0 Object Library". Currently I do this manually every time I migrate the database.

I'm attempting to do this via code. I have found some code via searches but have run into some problems.

One piece of code I found iterates through the collection of references. It determines if the reference is broken and then removes the reference before re-adding the reference:

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

   On Error Resume Next

   'Count the number of references in the database
   intCount = Access.References.Count
 
   'Loop through each reference in the database
   'and determine if the reference is broken.
   'If it is broken, remove the Reference and add it back.
   For intX = intCount To 1 Step -1
     Set loRef = Access.References(intX)
    'display reference path
     Debug.Print intX & ". " & loRef.Name & ": " & Access.References(intX).FullPath
     With loRef
       blnBroke = .IsBroken
       If blnBroke = True Or Err <> 0 Then
         strPath = .FullPath
         With Access.References
           .Remove loRef
           .AddFromFile strPath
         End With
       End If
      End With
   Next

However, in my situation, since the reference is "Missing" (as per viewing Tools-->References) the code is not working.

When it iterates over the item in the References collection that refers to the Excel 12 library object, the following "values" can be seen during debuging (by placing my cursor over them):

loRef.FullPath = <Method 'FullPath' of pbject 'Reference' failed>
loRef.Name = <Error in loading DLL>

Is there anyway that I can remove this "Missing" reference?

The reason I need to do so (or at least believe I need to do so) is that when I just run the following line of code:

'PATH_EXCEL_11 is a constant with the correct path/name
Access.References.AddFromFile PATH_EXCEL_11

I get the following error:

Run-time error '32813'
Name conflicts with existing module, project, or object library

Thanks,

- Bruce

 

[Remou]

It would be better to use "late binding". For example, instead of:

[tt]Dim xlObj As Excel.Application[/tt]

Use

[tt]Dim xlObj As Object

Set xlObj=Createobject("Excel.Application")[/tt]

This means that you will have to use values for all built-in Excel constants.

 

[shizekopf]

I'm recently started using Access 2007 to develop with, but my users are still on Access (Office) 2003. My database creates some Excel spreadsheets from reports, so it has been using the Microsoft Excel 11.0 Object Library. Now that I edited the database with Access 2007, the library has been replaced with Microsoft Excel 12.0 Object Library. This is a problem for my Access 2003 users because they do not have the 12.0 library available to them. Is there a way to make the database keep the 11.0 library?

I was able to resolve the problem by opening the database on a user's computer, referencing Microsoft Excel 11.0 Object Library, and saving the database back to the network location, but I'd rather not have to use Access 2003 to deploy my database.

I like the code posted by Bruce for setting the reference, but have the same problem - Run-time error '32813' - Name conflicts with existing module, project, or object library.

Does the use of "late binding" resolve this problem? The users got compilation errors as the database opened so they never even got to the point of trying to create a spreadsheet.

Is it possible to reference the 11.0 and the 12.0 object libraries?

Thanks for any help you can provide.

-Dave

 

[JoeAtWork]

Does the use of "late binding" resolve this problem? The users got compilation errors as the database opened so they never even got to the point of trying to create a spreadsheet.

Yes, use late binding as Remou suggested.

Resolve your "compilation" errors first, then come back to this one.

 

[bdbmeag]

Hi,

I haven't had a chance to try the late binding yet.

It doesn't appear that one can have both 11 and 12 at the same time. Primarily because Access 2003 does not recognize 12 and Access 2007 seems to automatically update 11 to 12.

Right now I'm a bit lucky as 2003 is a single server. When I develop in 2007 and move it to 2003 I update the reference manually.

Once I have some time (in a few hundred years!) I'll try using late binding!

Thanks,

- Bruce

 

[JoeAtWork]

It doesn't appear that one can have both 11 and 12 at the same time.

Even if you could, it would not solve your problem, since both have the exact same objects (example: Worksheet), and it would interpret which library you mean based on the precedence you order them on the References screen.

Late binding is the obvious answer and is not very hard to implement, should only require a few changes here and there. I don't understand why you are looking for an alternative answer.