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