ActiveX Data Objects Libraries References

[GPO]

About 18 months ago I developed a little tool in DAO and
Access 2000 (.mdb) that was deployed to about forty
different organisations, all on different systems. Nearly
always the users have been able to run it without the
problems you might associate with different OSs, different
updates and patches installed, and so on. All were using
Office 2000 professional, but were left to their own
devices as far as maintenance goes.

This year I was given the directive from on high to
rewrite the code in ADO. So I did. I have noticed in
testing that using ADO at work (we use 2.8 in here -
msado15.dll) is fine, but when I test it on my home
machine (ADO 2.7 - msado27.tlb), and the references don't
use the 2.7 library, they just denote 2.8 as missing. And
it dies.

Is there a way of telling the references "If you can't
find 2.8, then use the next most recent ADO library?"

To ensure compatibility, should I test in ADO 2.0 and
assume that if it runs in that environment, then it should
run in any subsequent version of ADO?

I also use a reference to Excel 9 (Excel9.olb), but what
happens if the people are using office XP or 2003 now? If
I assume the minimum requirements are Office 2000, then
can I include a piece of code that says "Look for Excel
2003, if you can't find it, go to Excel XP, if you can't
find that, go to Excel 2000"?

Thanks in advance.

Regards

GPO

 

[Douglas J. Steele]

There's no way of automating the fall-back to whatever version is present.
However, I thought virtually every version of ADO referenced msado15.dll.

While it may be possible to write code to check your references at startup
and fix them based on what's on the machine, the normal approach is to use
Late Binding, which doesn't require that a reference be made in advance.

Tony Toews has some information about this at
http://www.granite.ab.ca/access/latebinding.htm

 

[GPO]

Thanks Doug,

Some supplementary questions:
1. With late binding, is it true that performance takes a
hit, because each and every time you access something from
the library in question, Access needs to determine whether
that library suports what you're doing, and some sort of
messaging goes on between the Access and the other library?

2. What about references to different versions of MS
Excel? Is late binding the way to go here as well?

3. I thought that late binding didn't actually solve the
problem, it just allows the code to compile and run. Is
this true?

4. Your comment about msado15.dll was interesting. Is it
likely that my MS Office 2003 at home is just not
installed properly? It certainly doesn't seem to be
finding msado15.dll.

Thanks for Tony's link. I'll take a look.

GPO

 

[onedaywhen]

...

1. With late binding, is it true that performance takes a
hit

Yes. If you are concerned, do some test timings. I think you'll find
the degradation in performance is negligible but only you can decide.

2. What about references to different versions of MS
Excel? Is late binding the way to go here as well?

Yes, if you are prepared to put in the extra work. However, the object
model hasn't changed much from Excel9 to Excel11 so if you aren't
using the new features I personally can't see the incentive. Stick
with your 'lowest common denominator' i.e. Excel9.

3. I thought that late binding didn't actually solve the
problem, it just allows the code to compile and run. Is
this true?

It should solve the problem unless you've used something in ADO 2.8
which isn't in, or works differently to the equivalent in, ADO2.7. If
you have, late binding will allow you to code for any differences.

--

 

[Douglas J. Steele]

Absolutely. I've never noticed much of a hit late binding. I'm in an
environment where all the desktops are supposed to be the same, but I still
typically use late binding just to minimize future compatibility issues.

Regarding point 2, though, GPO mentioned that some of the people have Excel
2002 or Excel 2003, so he can't just stick with Excel9.

 

[GPO]

Does the following sound reasonable?
If I try:
Set objXL = GetObject(,"Excel.Application")
....and provided they all have an "Excel.Application" with
the same functionality I need, then it shouldn't matter
whether it's 2000, XP or 2003.

By the way, I downloaded and installed mdac28sdk.msi in
the hope that I would get an MSADO15.dll that represented
the ActiveX Data Objects 2.8 library, but when my home PC
refers to MSADO15.dll it still calls it 2.7. Have I
downloaded the right file (mdac28sdk.msi)? Or is something
else going on? Somewhere on google I saw something that
implied that ADO 2.8 was only relevant to Windows Server.

Any advice greatly appreciated.

 

[Douglas J. Steele]

I don't think you want the SDK (which is what it sounds as though you
downloaded)

The download for MDAC 2.8 is MDAC_TYP.exe

You can get it from
http://www.microsoft.com/downloads/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en

Since I'm sure that's going to word-wrap, go to
http://www.microsoft.com/data and select Downloads from the menu on the
left. Choose Product and Update Downloads, then MDAC Downloads from there.

 

[Douglas J. Steele]

Forgot to mention that your Excel example should work. It will start a new
instance of Excel even if they've got one used, but that hopefully won't be
a problem. If it is, take a look at what Dev Ashish does in
http://www.mvps.org/access/modules/mdl0006.htm at "The Access Web"

 

[GPO]

Thank you Douglas J Steele (do you never rest?)!

 

[onedaywhen]

Doug Steele, Microsoft Access MVP wrote ...

GPO mentioned that some of the people have Excel
2002 or Excel 2003, so he can't just stick with Excel9.

Why do you say that? Did you test? What do you think happens when you
open an Excel9 document in Excel11? Do you think you get a missing
reference? compile errors?

Here's my experience:

1. Open Excel 2000 (Excel9) with a new blank document.
2. Check the references in the VBE, note there is a reference to the
Microsoft Excel 9.0 Object Library.
3. Save the workbook.
4. Open Excel 2003 (Excel11).
5. Open the previously saved workbook.
6. Check the references in the VBE, note there is a reference to the
Microsoft Excel 11.0 Object Library, no missing references and no
compile errors.

--

 

[Douglas J. Steele]

Doug Steele, Microsoft Access MVP wrote ...


Why do you say that? Did you test? What do you think happens when you
open an Excel9 document in Excel11? Do you think you get a missing
reference? compile errors?

Here's my experience:

1. Open Excel 2000 (Excel9) with a new blank document.
2. Check the references in the VBE, note there is a reference to the
Microsoft Excel 9.0 Object Library.
3. Save the workbook.
4. Open Excel 2003 (Excel11).
5. Open the previously saved workbook.
6. Check the references in the VBE, note there is a reference to the
Microsoft Excel 11.0 Object Library, no missing references and no
compile errors.

Sorry, I don't see what that proves. Even if workbooks "correct" themselves,
what does that have to do with Access?

I suppose it's possible that I misinterpretted what GPO said, but I assumed
that he had developed an Access application that had a reference in it to
Excel 9. If that's the case, and you distribute your application to someone
who doesn't have Excel 9, the application will not work. It doesn't matter
whether they have a different version of Excel: Access will not pick up the
other version. And even though it's only the Excel reference that's broken,
it can impact your ability to use functions in any of the referenced
libraries.

 

[onedaywhen]

"onedaywhen" wrote...

Sorry, I don't see what that proves. Even if workbooks "correct" themselves,
what does that have to do with Access?

I assumed
that he had developed an Access application that had a reference in it to
Excel 9. If that's the case, and you distribute your application to someone
who doesn't have Excel 9, the application will not work. It doesn't matter
whether they have a different version of Excel: Access will not pick up the
other version.

Like I said, did you test?

I'll try again. It's a bit difficult to test the stipulation
'distribute your application to someone who doesn't have Excel 9 (but
does have Excel10 or Excel11)' but hopefully I can do the equivalent
on my machine on which I have both Office 2000 and Office 2003
installed:

1. Uninstall Excel11.
2. Open Access2003 (Access11), create and save a new blank .mdb file
3. Create a new module.
4. Go to the references in the VBE, set a reference to reference to
the Excel9 object library (EXCEL9.OLB).
5. Type the following code:

Private xl As Excel.Application
Sub test()
Set xl = New Excel.Application
xl.Visible = True
End Sub

6. Run the code, note the version of the created instance of the Excel
app is Excel9.
7. Save and exit the .mdb.
8. Fully uninstall Office2000, note that EXCEL9.OLB has been removed.
9. Reinstall Excel11.
10. Open my previously created .mdb in Access11.
11. Check the references in the VBE, note there is now a reference to
Excel11, no missing references and no compile errors.
12. Run the code, note the version of the created instance of the
Excel app is Excel11 and no run-time errors.

If I have done something above that does not accurately recreate the
scenario you describe and which you say would not work, then I would
genuinely (and humbly) be interested to hear from you.

--

 

[Douglas J. Steele]

...


Like I said, did you test?

I'll try again. It's a bit difficult to test the stipulation
'distribute your application to someone who doesn't have Excel 9 (but
does have Excel10 or Excel11)' but hopefully I can do the equivalent
on my machine on which I have both Office 2000 and Office 2003
installed:

1. Uninstall Excel11.
2. Open Access2003 (Access11), create and save a new blank .mdb file
3. Create a new module.
4. Go to the references in the VBE, set a reference to reference to
the Excel9 object library (EXCEL9.OLB).
5. Type the following code:

Private xl As Excel.Application
Sub test()
Set xl = New Excel.Application
xl.Visible = True
End Sub

6. Run the code, note the version of the created instance of the Excel
app is Excel9.
7. Save and exit the .mdb.
8. Fully uninstall Office2000, note that EXCEL9.OLB has been removed.
9. Reinstall Excel11.
10. Open my previously created .mdb in Access11.
11. Check the references in the VBE, note there is now a reference to
Excel11, no missing references and no compile errors.
12. Run the code, note the version of the created instance of the
Excel app is Excel11 and no run-time errors.

If I have done something above that does not accurately recreate the
scenario you describe and which you say would not work, then I would
genuinely (and humbly) be interested to hear from you.

Sorry, but what you're describing does not correspond to what I've observed.

For one thing, whenever I choose Microsoft Excel x Object Library from the
list of available references, Access sets a reference to Excel.EXE, not to
an OLB.

I suppose it's possible that the process of uninstalling and reinstalling is
somehow fixing the references. I doubt very much, though, that if you took
your application to another machine with a different version of Access that
it would work.

 

[onedaywhen]

...

Sorry, but what you're describing does not correspond to what I've observed.

But did you test?

For one thing, whenever I choose Microsoft Excel x Object Library from the
list of available references, Access sets a reference to Excel.EXE, not to
an OLB.

I suppose it's possible that the process of uninstalling and reinstalling is
somehow fixing the references. I doubt very much, though, that if you took
your application to another machine with a different version of Access that
it would work.

Hard to please, eh <g>. OK, new approach required, so I:

1. Get new software, VMWare Workstation (http://www.vmware.com/).
2. Spend significant time learning how to build a virtual machine.
3. Install MS Office 2003 (including Access11) on a totally 'clean'
virtual machine.
4. Copy the aforementioned .mdb (the one with the reference to Excel9)
to a 'network' location that is visible to the virtual machine.
5. On the virtual machine, open the .mdb.
6. Check the references in the VBE, note there is now a reference to
Excel11 (you're right, it is the .exe!), no missing references and no
compile errors.
7. Run the code, note the version of the created instance of the Excel
app is Excel11 and no run-time errors.

--

 

[Douglas J. Steele]

...
observed.

But did you test?


Hard to please, eh <g>. OK, new approach required, so I:

1. Get new software, VMWare Workstation (http://www.vmware.com/).
2. Spend significant time learning how to build a virtual machine.
3. Install MS Office 2003 (including Access11) on a totally 'clean'
virtual machine.
4. Copy the aforementioned .mdb (the one with the reference to Excel9)
to a 'network' location that is visible to the virtual machine.
5. On the virtual machine, open the .mdb.
6. Check the references in the VBE, note there is now a reference to
Excel11 (you're right, it is the .exe!), no missing references and no
compile errors.
7. Run the code, note the version of the created instance of the Excel
app is Excel11 and no run-time errors.

Okay, okay. Just to keep you happy, I finally got around to testing. You're
correct: Access seems capable of resolving different versions of Excel and
Word. Much to my surprise, it even worked with MDEs. However, it doesn't
work for all applications. As well, if you take your application to a
machine that doesn't have any version of Excel installed, having a reference
set will cause the entire application to stop working, whereas using Late
Binding would mean that only that part of the application that was dependent
on Excel wouldn't work.

 

[onedaywhen]

...

Just to keep you happy, I finally got around to testing.

Don't do it for my sake. Think about your position as an MVP: if you
say something erroneous with certainty, readers will mistake it for
the Truth. I don't think it's too much to ask you to test before you
*contradict* someone, especially one who lacks a (good) reputation.
I've read the FAQ (http://www.mvps.org/access/netiquette.htm) and it
says (10.e):

If you are 90% sure, say 'I think...'

Damn, I've just noticed FAQ 10.f:

The use of your real name, unlike some juvenile
handle, says 'please take me seriously.'

Easy on the custard for that slice of humble pie, thanks.

--