Loading a Reference

[scott]

I need to load the "Microsoft ActiveX Data Objects 2.6 Library" when a
particular worksheet opens. I alreafdy plan to create an add-in to hold my
code and special menu.

Can VBA load a reference? Any links to examples?

 

[Guest]

In the VBA editor, go to Tools, References, then add the specific ADO.

Kou

 

[scott]

I know how to do that, I was asking if it's possible with VBA.

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;160647
XL97: How to Programmatically Create a Reference

applicable to Excel 97 and later.

 

[scott]

Hey Tom, I'm just trying to load a Reference Library of ADO 2.6, although
your link was helpful in another way.

 

[scott]

i'm sorry, i think that's going to work. i though your link was just
accessing functions on another sheet.

 

[Jamie Collins]

I need to load the "Microsoft ActiveX Data Objects 2.6 Library" when a
particular worksheet opens.

Must it be ADO 2.6? Are you sure all your users have this version e.g.
what do you want to happen if they only have ADO 2.8? Rather than
setting a reference (early binding), you may be able to use late
binding e.g. instead of:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

you could use:

Dim rs As Object
On Error Resume Next
Set rs = CreateObject("ADODB.Recordset")
If rs Is Nothing Then
MsgBox "ADO may not be installed."
....

AFAIK You cannot use CreateObject to specify a version; if a machine
has multiple versions, as does mine, you just get the most recently
installed version. With MDAC you can be assured the most recently
installed will have the highest version number.

If it must be ADO 2.6, you may want to check the registry for the
component's progid before attempting to set the reference.
Jamie.

--

 

[NickHK]

Jamie,
You can certainly:
Set obj1 = CreateObject("ADODB.Recordset.2.5")
Set obj2 = CreateObject("ADODB.Recordset.2.7")

That's if you really eanted to

NickHK

 

[Jamie Collins]

You can certainly:
Set obj1 = CreateObject("ADODB.Recordset.2.5")
Set obj2 = CreateObject("ADODB.Recordset.2.7")

For the recordset object, I have three registry keys:

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\ADODB.Recordset
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\ADODB.Recordset.2.8
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\ADODB.Recordset.2.7

However, they all share the same CLSID, so it doesn't matter which name
I use in CreateObject, I'll always get an object from the same
component.

My machine also has version ADO 2.6 available as a reference, but when
I try

Set obj1 = CreateObject("ADODB.Recordset.2.6")

I get the expected error, 'ActiveX component can't create object'.
Jamie.

--