Referencing Libraries through code.

[Purnima]

Hi,

I have a tool developed in VBA which references the ActiveX Data Obj Library
2.8. Now this code runs fine on some machines but on others it gives a
reference error. So I have to then add the reference to the ADO library on
that machine and run the code. Is it possible to overcome this problem in
some way and run the code on all machines without any error? If I somehow add
the libraries through coding, will it help? And if yes, how do I add the
libraries through code itself rather than going to the Tools menu and
selecting the reference?

Any help is appreciated.

Thanx.

 

[Bob Phillips]

You could do one of two things:

Install the code on the lowest version of Excel that you will deploy it to,
and recompile until you have no errors, and then deploy to the later
versions.

Use lat binding. With this you don't set a reference in the project, VBA
will get the library at run-time. For late binding, you need to declare all
of the specific objects types as Object, and change any of those library
constants to their numeric equivalent. So for instance, instead of

Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

sSQL = "SELECT * FROM [Sales$A1:E89]"
Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
Sheet1.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No rec"
End If


You might use

Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sales$A1:E89]"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
Sheet1.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No rec"
End If


This is covered, albeit for an Outlook connection, at
http://www.xldynamic.com/source/xld.EarlyLate.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RB Smissaert]

There is a third option that works fine for me, but I am not sure it always
works.
Save the workbook without the ADO reference.
Via the workbook Open event run this Sub:

Sub AddADO()

Dim R

For Each R In ThisWorkbook.VBProject.References
If R.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And R.Major = 2
Then
Exit Sub
End If
Next

On Error GoTo NOTFOUND

'although usually the ADO version will be higher, doing Minor:=0 will
install
'the higher version if available. On the other hand when you specify
Minor:=5
'and only a lower version is available, this can't be installed
'----------------------------------------------------------------------------
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _
Major:=2, Minor:=0
Exit Sub

NOTFOUND:
On Error GoTo 0

End Sub

This will add the ADO reference that is available on the user's machine.


So, you will get:

Private Sub Workbook_Open()
AddADO
End Sub


RBS