Trying to come up with a foolproof way to add the current ADO library to the
project's references.

I used to do this by just saving the .xla with a reference to a low version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development machine,
2.1
In the Workbook_Open event remove this reference and add the current library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert: ", _
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem to be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

Why not just using late binding?


Dim oConn As Object
Dim oRS As Object

Set oConn = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert: ",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

What Bob recommends is foolproof but note that there is a slight performance
hit when you do it this way. That is the tradeoff you make for doing it this
way.

The other issue is that you loose intellisence when you are writing the
code. My preference when doing it this way is to reference the ADO object and
write all of my dim statements referencing the actual ADO objects. Once the
code is working then remove the reference and change the dim statements to
use the late binding Bob listed. Maybe it is just me but I find it difficult
to program without intelliesence...
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

Why not just using late binding?


Dim oConn As Object
Dim oRS As Object

Set oConn = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert: ",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

I don't want to use late binding for 3 reasons:
No intelli-sense
Some performance penalty?
As this is a very large .xla a lot of code re-writing.

RBS

"Bob Phillips" wrote in message
...
Why not just using late binding?


Dim oConn As Object
Dim oRS As Object

Set oConn = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry
reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert:
",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

Pick the lowest version of ADO that you have on your system and save it using
that reference. Your code should still run and most people will have version
2.5 or better. Otherwise you are stuck with the re-write... The intellisence
is pretty easy to work around so the only real issue is the performance hit
which is probably not substantial enough to be a show stopper...
--
HTH...

Jim Thomlinson


"RB Smissaert" wrote:

I don't want to use late binding for 3 reasons:
No intelli-sense
Some performance penalty?
As this is a very large .xla a lot of code re-writing.

RBS

"Bob Phillips" wrote in message
...
Why not just using late binding?


Dim oConn As Object
Dim oRS As Object

Set oConn = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry
reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert:
",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

One drawback of that is that if I save with say 2.1 and the user has 2.1 and
2.8 on the system it will stick with 2.1.
Not sure if there is much harm in that, but maybe 2.8 has better
performance.
What is wrong with the posted code?

RBS

"Jim Thomlinson" wrote in message
...
Pick the lowest version of ADO that you have on your system and save it
using
that reference. Your code should still run and most people will have
version
2.5 or better. Otherwise you are stuck with the re-write... The
intellisence
is pretty easy to work around so the only real issue is the performance
hit
which is probably not substantial enough to be a show stopper...
--
HTH...

Jim Thomlinson


"RB Smissaert" wrote:

I don't want to use late binding for 3 reasons:
No intelli-sense
Some performance penalty?
As this is a very large .xla a lot of code re-writing.

RBS

"Bob Phillips" wrote in message
...
Why not just using late binding?


Dim oConn As Object
Dim oRS As Object

Set oConn = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library
to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry
reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As
Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True
Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert:
",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is
so
tricky I can't be
careful enough. The .ini file read should always make it possible for
the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem
to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

Hi Bart,

Your add ado ref routine both worked and failed getting your error message.
Somehow it managed to add the ref to v2.8 though as far as I know this
version is not correctly registered on my system. It doesn't normally appear
in tools ref's and things have gone wrong in the past using other people's
wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which
for some reason I find more reliable.

As I say it added the 2.8 ref but when I did this

' r = a vba ref to v2.8
Debug.Print r.Name, r.Description, r.Major, r.Minor
ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8

Debug.Print r.FullPath
' this fails !!
debug.print err, err.description
-2147319779 Method 'FullPath' of object 'Reference' failed

If I change the ref to v2.7 both debug lines work
ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
C:\Program Files\Common Files\System\ado\msado27.tlb

Maybe something similar to your user.

I have two versions of msado15.dll on my system, one an old v1.5 and the
newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the
other versions in your list.

FWIW I recall having problems when trying to upgrade to 2.8, it was a while
ago.

Regards,
Peter T

PS I commented out your code to get file name from an ini and did -
strADOPathFromINI = ThisWorkbook.Name

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert: ",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

Hi Peter,

Thanks for the reply, but I don't get it quite, other than that you say
avoid 2.8.
You are talking about the reference object, but I haven't used that in
that code.
Correct me if I misunderstood.
Doing this from work, so maybe I didn't look properly.

RBS


Peter T wrote:
Hi Bart,

Your add ado ref routine both worked and failed getting your error message.
Somehow it managed to add the ref to v2.8 though as far as I know this
version is not correctly registered on my system. It doesn't normally appear
in tools ref's and things have gone wrong in the past using other people's
wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which
for some reason I find more reliable.

As I say it added the 2.8 ref but when I did this

' r = a vba ref to v2.8
Debug.Print r.Name, r.Description, r.Major, r.Minor
ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8

Debug.Print r.FullPath
' this fails !!
debug.print err, err.description
-2147319779 Method 'FullPath' of object 'Reference' failed

If I change the ref to v2.7 both debug lines work
ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
C:\Program Files\Common Files\System\ado\msado27.tlb

Maybe something similar to your user.

I have two versions of msado15.dll on my system, one an old v1.5 and the
newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the
other versions in your list.

FWIW I recall having problems when trying to upgrade to 2.8, it was a while
ago.

Regards,
Peter T

PS I commented out your code to get file name from an ini and did -
strADOPathFromINI = ThisWorkbook.Name

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert: ",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

Peter,

Just noticed that the function misses this line at the end:

AddReferenceFromFile = True

Before Exit Function

Try again with that added.

RBS


Peter T wrote:
Hi Bart,

Your add ado ref routine both worked and failed getting your error message.
Somehow it managed to add the ref to v2.8 though as far as I know this
version is not correctly registered on my system. It doesn't normally appear
in tools ref's and things have gone wrong in the past using other people's
wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which
for some reason I find more reliable.

As I say it added the 2.8 ref but when I did this

' r = a vba ref to v2.8
Debug.Print r.Name, r.Description, r.Major, r.Minor
ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8

Debug.Print r.FullPath
' this fails !!
debug.print err, err.description
-2147319779 Method 'FullPath' of object 'Reference' failed

If I change the ref to v2.7 both debug lines work
ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
C:\Program Files\Common Files\System\ado\msado27.tlb

Maybe something similar to your user.

I have two versions of msado15.dll on my system, one an old v1.5 and the
newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the
other versions in your list.

FWIW I recall having problems when trying to upgrade to 2.8, it was a while
ago.

Regards,
Peter T

PS I commented out your code to get file name from an ini and did -
strADOPathFromINI = ThisWorkbook.Name

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert: ",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

Hi Bart,

Yes I had noticed (later) the function didn't return True on success.
However that doesn't change what I reported earlier about v2.8 not working
correctly in my system. Trying to debug its Fullpath (after setting the ref)
errors.

In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but
I know it will cause problems).
I ran same code in XL97, v2.8 is also checked but marked MISSING.

Regards,
Peter


wrote in message
ups.com...
Peter,

Just noticed that the function misses this line at the end:

AddReferenceFromFile = True

Before Exit Function

Try again with that added.

RBS


Peter T wrote:
Hi Bart,

Your add ado ref routine both worked and failed getting your error
message.
Somehow it managed to add the ref to v2.8 though as far as I know this
version is not correctly registered on my system. It doesn't normally
appear
in tools ref's and things have gone wrong in the past using other
people's
wb's with this ref. So I always end up changing to 2.7 or rather 2.5
which
for some reason I find more reliable.

As I say it added the 2.8 ref but when I did this

' r = a vba ref to v2.8
Debug.Print r.Name, r.Description, r.Major, r.Minor
ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8

Debug.Print r.FullPath
' this fails !!
debug.print err, err.description
-2147319779 Method 'FullPath' of object 'Reference' failed

If I change the ref to v2.7 both debug lines work
ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
C:\Program Files\Common Files\System\ado\msado27.tlb

Maybe something similar to your user.

I have two versions of msado15.dll on my system, one an old v1.5 and the
newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all
the
other versions in your list.

FWIW I recall having problems when trying to upgrade to 2.8, it was a
while
ago.

Regards,
Peter T

PS I commented out your code to get file name from an ini and did -
strADOPathFromINI = ThisWorkbook.Name

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library
to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry
reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _

"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As
Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True
Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert:
",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is
so
tricky I can't be
careful enough. The .ini file read should always make it possible for
the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem
to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

Hi Peter,

My customers ( 100 ) have been using this routine now for a week and
sofar
I haven't heard about any problems. Are you suggesting I should leave
the 2.8
option out?

RBS

Peter T wrote:
Hi Bart,

Yes I had noticed (later) the function didn't return True on success.
However that doesn't change what I reported earlier about v2.8 not working
correctly in my system. Trying to debug its Fullpath (after setting the ref)
errors.

In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but
I know it will cause problems).
I ran same code in XL97, v2.8 is also checked but marked MISSING.

Regards,
Peter


wrote in message
ups.com...
Peter,

Just noticed that the function misses this line at the end:

AddReferenceFromFile = True

Before Exit Function

Try again with that added.

RBS


Peter T wrote:
Hi Bart,

Your add ado ref routine both worked and failed getting your error
message.
Somehow it managed to add the ref to v2.8 though as far as I know this
version is not correctly registered on my system. It doesn't normally
appear
in tools ref's and things have gone wrong in the past using other
people's
wb's with this ref. So I always end up changing to 2.7 or rather 2.5
which
for some reason I find more reliable.

As I say it added the 2.8 ref but when I did this

' r = a vba ref to v2.8
Debug.Print r.Name, r.Description, r.Major, r.Minor
ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8

Debug.Print r.FullPath
' this fails !!
debug.print err, err.description
-2147319779 Method 'FullPath' of object 'Reference' failed

If I change the ref to v2.7 both debug lines work
ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
C:\Program Files\Common Files\System\ado\msado27.tlb

Maybe something similar to your user.

I have two versions of msado15.dll on my system, one an old v1.5 and the
newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all
the
other versions in your list.

FWIW I recall having problems when trying to upgrade to 2.8, it was a
while
ago.

Regards,
Peter T

PS I commented out your code to get file name from an ini and did -
strADOPathFromINI = ThisWorkbook.Name

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO library
to
the
project's references.

I used to do this by just saving the .xla with a reference to a low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from registry
reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _

"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As
Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True
Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert:
",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this is
so
tricky I can't be
careful enough. The .ini file read should always make it possible for
the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't seem
to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS

Hi Bart,

I wouldn't want to suggest you leave out 2.8, not sure what the problem is
with it in my system and probably unusual. No idea if it's relates to the
reason your user fails.

I modified your function and it succeeded it correctly adding v2.7 after
first adding the problematic 2.8.

Function AddReferenceFromFile( blah
Dim oRef As Object ' Reference
' code

On Error Resume Next
Set oRef = VBProj.References.AddFromFile(strFilePath)

' AddReferenceFromFile = True
AddReferenceFromFile = Len(oRef.fullPath) 0

If Err.Number Then
If Not oRef Is Nothing Then
VBProj.References.Remove oRef ' this removed my 2.8
End If
Else
AddReferenceFromFile = True
End If

Regards,
Peter T

wrote in message
oups.com...
Hi Peter,

My customers ( 100 ) have been using this routine now for a week and
sofar
I haven't heard about any problems. Are you suggesting I should leave
the 2.8
option out?

RBS

Peter T wrote:
Hi Bart,

Yes I had noticed (later) the function didn't return True on success.
However that doesn't change what I reported earlier about v2.8 not
working
correctly in my system. Trying to debug its Fullpath (after setting the
ref)
errors.

In my XL2000 looking at tools ref's v2.8 is checked and looks correct
(but
I know it will cause problems).
I ran same code in XL97, v2.8 is also checked but marked MISSING.

Regards,
Peter


wrote in message
ups.com...
Peter,

Just noticed that the function misses this line at the end:

AddReferenceFromFile = True

Before Exit Function

Try again with that added.

RBS


Peter T wrote:
Hi Bart,

Your add ado ref routine both worked and failed getting your error
message.
Somehow it managed to add the ref to v2.8 though as far as I know
this
version is not correctly registered on my system. It doesn't
normally
appear
in tools ref's and things have gone wrong in the past using other
people's
wb's with this ref. So I always end up changing to 2.7 or rather 2.5
which
for some reason I find more reliable.

As I say it added the 2.8 ref but when I did this

' r = a vba ref to v2.8
Debug.Print r.Name, r.Description, r.Major, r.Minor
ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8

Debug.Print r.FullPath
' this fails !!
debug.print err, err.description
-2147319779 Method 'FullPath' of object 'Reference' failed

If I change the ref to v2.7 both debug lines work
ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
C:\Program Files\Common Files\System\ado\msado27.tlb

Maybe something similar to your user.

I have two versions of msado15.dll on my system, one an old v1.5 and
the
newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have
all
the
other versions in your list.

FWIW I recall having problems when trying to upgrade to 2.8, it was
a
while
ago.

Regards,
Peter T

PS I commented out your code to get file name from an ini and did -
strADOPathFromINI = ThisWorkbook.Name

"RB Smissaert" wrote in message
...
Trying to come up with a foolproof way to add the current ADO
library
to
the
project's references.

I used to do this by just saving the .xla with a reference to a
low
version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.

Then I had a method that got the ADO library file path from
registry
reads
using code from KeepITCool:

Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _

"winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function

Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others,
not
exactly
sure why.

Currently I use this method:
Save the .xla with the lowest ADO version I have on my development
machine,
2.1
In the Workbook_Open event remove this reference and add the
current
library
like this,
slightly simplified:

Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As
Boolean

Dim VBProj As VBProject

On Error GoTo ERROROUT

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

VBProj.References.AddFromFile strFilePath

Exit Function
ERROROUT:

End Function


Sub SetADOReference()

Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles

Const strINIPath As String = "C:\test.ini"

strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")

If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If

strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"

Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing

Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select

If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If

arrADOFiles = Array("msado15.dll", "msado27.tlb",
"msado26.tlb", _
"msado25.tlb", "msado21.tlb",
"msado20.tlb")

For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) =
True
Then
Exit Sub
End If
Next

MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert:
",
_
vbExclamation, "adding ADO reference"

End Sub


Sofar this seems to work fine. I know it is overkill, but as this
is
so
tricky I can't be
careful enough. The .ini file read should always make it possible
for
the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then
there
always
is the brute force
method with the array.

Have read a lot of postings about this problem, but there doesn't
seem
to
be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?


RBS