References Issue 2007 vs 2003

[LarryP]

Our company is in transition: we have some people on older PCs with Office
2003, some on new PCs with 2007, and a growing group on older PCs who elected
to upgrade themselves. In the latter case the company's upgrade policy gave
them Word/Excel/Powerpoint 2007, but left Access 2003 intact.

We're finding that if we modify databases on a 2007 PC, it creates
references to Access12 and Excel12, and people still entirely in the 2003
mode get "missing reference" errors. Conversely, if we remember to remove
those references and restore the Access11 and Excel11 references, people in
either of the 2007 categories get errors because they no longer have an
Excel.exe in their Office11 folder.

I T H I N K what we need is code that will set the appropriate
references on startup depending on what version of Office/Excel/Access the
user is running. Can anyone (1) confirm that thinking, and (2) provide code
snippet(s) that will do it? Would be greatly appreciated.

 

[Douglas J. Steele]

Your database should be split into a front-end (containing the queries,
forms.reports. macros and modules), linked to a back-end. (This has nothing
to do with your change from 2003 to 2007)

Each user should have his/her own copy of the front-end, preferably on
his/her hard drive (only the back-end should be shared)

You should create separate front-ends for the Access 2003 users and the
Access 2007 users;

 

[LarryP]

As to (1) and (2), that's how we're set up. As to (3), was hoping to avoid
having to maintain two separate front ends, as we may be in this
fence-straddling situation for many months before everyone has gotten aboard
the 2007 wagon. Thought maybe one could write code to determine user's
version and set references accordingly. If that's not do-able with VBA, will
have to rethink the problem.

 

[Douglas J. Steele]

What references do you have in your application?

If anything other than the basic built-in ones, you might consider using
Late Binding.

 

[LarryP]

Alright, now you've caught me with my knickers around my knees. The whole
early/late binding thing is still something of a mystery to me.

That notwithstanding: the references that are proving challenging are
Microsoft xxxxxx ##.0 Object Library, where xxxxxx is either Excel or Access
and ## is either 11.0 or 12.0. People still in the 2003 environment are
looking for the 11.0 references, but people who have a new computer with 2007
or who have elected to upgrade to 2007 are looking for the 12.0 references.

So if we push out a database developed (and that might just mean changed in
minor ways) on a 2007 computer, it has the 12.0 references and 2003 people
have problems. On the other hand, if we remember to go the extra step and
set the references back to 11.0 level, people on 2007 computers have problems
because by upgrading they nuked the older references.

So my hope is (was?) that one can write VBA to check version at startup and
adjust the references accordingly. Yes? No?

 

[Douglas J. Steele]

I believe that the Access reference should correct itself.

For Excel, you really should use Late Binding. See whether what Tony Toews
has at http://www.granite.ab.ca/access/latebinding.htm is enough to get you
going. You could also read my July, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

While it is theoretically possible to adjust the references, I'd advise
against it.