How to Indentify ODBC drivers installed on a Machine using VBA

[HelpNeeded]

Hi,

I have an excel workbook containing database queries that uses ODBC driver to update data. However different machines have different versions of the ODBC drivers installed and so although the code works on my machine, it doesn't on others because the ODBC connection reference is different.

So as a first step, I would like Excel to automatically find what ODBC drivers are installed when the macro is run so that I could dynamically change the ODBC connection reference depending on what drivers are installed.

The connection strings relating to ODBC drivers are in following formats:

Machine A: "ODBC;DRIVER={Oracle OraDb11g_home1}
Machine B: "ODBC;DRIVER={Oracle in instantclient11_1}
ETC

Can this be done? I'm not sure if this is what I need but I found the code below on the net to find a list of drivers but it does not seem to work, the macro just freezes and debugging takes me to the line:

".RemoveItem (i)"

Thanks,

HB
--------------------------------------------------------
[VBA]

Private Declare Function SQLDataSources Lib "ODBC32.DLL" _
(ByVal henv&, ByVal fDirection%, ByVal szDSN$, ByVal cbDSNMax%, _
pcbDSN%, ByVal szDescription$, ByVal cbDescriptionMax%, _
pcbDescription%) As Integer
Private Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)
Const SQL_SUCCESS As Long = 0
Const SQL_FETCH_NEXT As Long = 1

Sub GetDSNsAndDrivers()
'The program works by utilizing the SQLDataSources API of the ODBC32.DLL.

Dim i As Integer
Dim sDSNItem As String * 1024
Dim sDRVItem As String * 1024
Dim sDSN As String
Dim sDRV As String
Dim iDSNLen As Integer
Dim iDRVLen As Integer
Dim lHenv As Long 'handle to the environment
On Error Resume Next
cboDSNList.AddItem "(None)"
'get the DSNs
If SQLAllocEnv(lHenv) <> -1 Then
Do Until i <> SQL_SUCCESS
sDSNItem = Space$(1024)
sDRVItem = Space$(1024)
i = SQLDataSources(lHenv, SQL_FETCH_NEXT, sDSNItem, 1024, _
iDSNLen, sDRVItem, 1024, iDRVLen)
sDSN = Left$(sDSNItem, iDSNLen)
sDRV = Left$(sDRVItem, iDRVLen)

If sDSN <> Space(iDSNLen) Then
cboDSNList.AddItem sDSN
cboDrivers.AddItem sDRV '---optional - driver
'--- value returned
End If
Loop
End If
'remove the dups
If cboDSNList.ListCount > 0 Then
With cboDrivers
If .ListCount > 1 Then
i = 0
While i < .ListCount
If .List(i) = .List(i + 1) Then
.RemoveItem (i)
Else
i = i + 1
End If
Wend
End If
End With
End If
cboDSNList.ListIndex = 0
End Sub
[/VBA]

[Simon Lloyd]

Lucas, you have crossposted this thread and had an answer under a different username here http://www.thecodecage.com/forumz/me...tml#post740906

Please post the links to ALL your crossposts here and at ALL the otherplaces you have crossposted, to understand why we ask for this please read the link in my signature!