Hello. I just started a new job where we use Access 2013 on a daily basis. Mine references Microsoft Excel 15.0 Object Library, but everyone else in the office has Excel 14.0. So anytime I open access, it defaults everyone to 15.0, which they don't have,
so it breaks everything and they have to open up their reference list and change it back. Every. Time. This is obviously a big old waste of time and not really sustainable, so I'm hoping to find a fix. There must be some kind of code to remedy this issue,
right? I'm no developer and can't even seem to Google the right thing, so any tips or nudges in the right direction would be appreciated. Thanks so much.
If you're sharing a database, the ideal architecture is to split the database into a back-end database (containing only the tables) and a front-end database (containing the queries, forms, reports, macros, and modules) with linked tables in the front-end
pointing to the tables in the back-end. The back-end is then stored on a network share, and each user gets their own copy of the front-end to store and work with on their own workstation. There are many benefits to this split architecture, and
among them is the fact that differences in the Office configuration on various users' computers don't affect other users, since the references are set in their personal copies of the front-end.
That said, you can also avoid version-reference problems of the sort you describe by doing what is called "late binding" in your usage of Excel. To implement late binding, you remove the reference to the Excel library altogether, and change
your code so that it doesn't depend on the Excel library for the object types and constants. For example, where you may currently have code like this:
Dim objXL As Excel.Application
Dim objWKS As Excel.Worksheet
' ...
Set objXL = New Excel.Application
with late binding, you would have code like this:
Dim objXL As Object
Dim objWKS As Object
' ...
Set objXL = CreateObject("Excel.Application")
You also have to watch out for named constants that are defined in the Excel library, and either define them yourself with the appropriate values, or else replace the constant name with the value.
This is a bit of a pain for a developer to set up at first, since you lose the intellisense that you get from the Excel library, but what I do is start out in my development with early binding, and then switch to late binding when I'm done.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
