API call to get List of ODBC DSNs

Vlip · Mar 25, 2002

Hi,

Does anyone have an API that will allow me to get a list of ODBC data sources defined in the Windows ODBC Control Panel?

Thanks in advance,
Vlip

Vlip · Apr 2, 2002

I'm sorry; we're still not on the same playing field. I will give this one more try and then I will just give up.
Here goes: If you open the ODBC control panel (on Windows 2000 it is in "Administrative Tools" then "Data Sources (ODBC)") you will get a dialog box titled: ODBC Data Source Administrator with 7 tabs along the top. The first two are "User DSN" and "System DSN". On either of these tabs, there is a two column listbox called: "User Data Sources" and "System Data Sources" respectively. Each list has two columns labeled "Name" and "Driver". I want that same list. I don't need the driver column, just the name column. If I can get the User DSN list then, I can get the other. The only difference is the path and that has been amply spelled out. So, just tell me how to get that list and we'll be home free! Again, I'm sorry I've taken up so much of your time. I appreciate your help.

Best regards,
Vlip

Jack in the UK · Apr 2, 2002

Sorry a wild stab, can you not importthe same table twice ubder ODBC MS Query ruling this will work,, else i suggets asking the IT dept, i cant see the data from ghere somethings a drif..i agree..

sorry of little help... i wish i could do more, i really do..

thought...

winy2k do you use Kopen3Live???

jsheehan · Apr 2, 2002

is trghe oais abou as usdefyk as jacks>

> >???

anywone oalsww have a duckidn ckluse?

Jack in the UK · Apr 2, 2002

Sorry Im English and poor English at that, German i guess sorry need translation...

Legacy 98055 · Apr 3, 2002

Hi
This will load a string array with user and sys DSNs.
As is, places on sheet1 A1:A?
Edit to suit.
If this is not what you need then I can not help you.
Good Day.
Tom

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Private Declare Function SQLDataSources Lib "odbc32.dll" _
 (ByVal hEnv As Long, _
 ByVal fDirection As Integer, _
 ByVal szDSN As String, _
 ByVal cbDSNMax As Integer, _
 pcbDSN As Integer, _
 ByVal szDescription As String, _
 ByVal cbDescriptionMax As Integer, _
 pcbDescription As Integer) As Long

 Private Declare Function SQLAllocHandle Lib "odbc32.dll" _
 (ByVal HandleType As Integer, _
 ByVal InputHandle As Long, _
 OutputHandlePtr As Long) As Long

 Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
 (ByVal EnvironmentHandle As Long, _
 ByVal dwAttribute As Long, _
 ByVal ValuePtr As Long, _
 ByVal StringLen As Long) As Long

 Private Declare Function SQLFreeHandle Lib "odbc32.dll" _
 (ByVal HandleType As Integer, _
 ByVal Handle As Long) As Long

 Private Const DSN_LEN As Long = 32
 Private Const DESC_LENGTH As Long = 128
 Private Const FoundOk As Long = 0
 Private Const GetNext As Long = 1
 Private Const Null_HANDLE As Long = 0
 Private Const HANDLE_ENV As Long = 1
 Private Const ATTR_ODBC_VERSION As Long = 200
 Private Const OV_ODBC3 As Long = 3
 Private Const IS_INTEGER As Long = (-6)
 Private UserSysStringArray() As String

 Sub UserSysDSNs()

 Dim hEnv As Long
 Dim sServer As String
 Dim sDriver As String
 Dim nSvrLen As Integer
 Dim nDvrLen As Integer
 Dim ArrayCntr As Integer
 ArrayCntr = 0



 If SQLAllocHandle(HANDLE_ENV, _
 Null_HANDLE, hEnv) <> 0 Then

 If SQLSetEnvAttr(hEnv, _
 ATTR_ODBC_VERSION, _
 OV_ODBC3, _
 IS_INTEGER) <> 0 Then

 sServer = Space$(DSN_LEN)
 sDriver = Space$(DESC_LENGTH)

 Do While SQLDataSources(hEnv, _
 GetNext, _
 sServer, _
 DSN_LEN, _
 nSvrLen, _
 sDriver, _
 DESC_LENGTH, _
 nDvrLen) = FoundOk

 ArrayCntr = ArrayCntr + 1
 ReDim Preserve UserSysStringArray(1 To ArrayCntr)
 UserSysStringArray(ArrayCntr) = Left$(sServer, nSvrLen)
 sServer = Space$(DSN_LEN)
 Loop
 End If
 Call SQLFreeHandle(HANDLE_ENV, hEnv)
 End If

 ArrayCntr = 0

 Do Until ArrayCntr = UBound(UserSysStringArray)
 ArrayCntr = ArrayCntr + 1
 Sheet1.Range("A" & ArrayCntr).Value = UserSysStringArray(ArrayCntr)
 Loop

 End Sub

</td></tr></table>

Legacy 98055 · Apr 3, 2002

P.S.
I'm assuming that your are fairly familiar with VBA. If not let me know. Seeing that you have went through so much trouble, I tested this code on two machines. Win ME and W2k. And then cross referenced the list produced with that in the registry. Both were correct. Hope it helps you.
I did not have any problem with the API as part of the user group(Microsoft's Default settings for the user group) in Win2k.
Tom

Vlip · Apr 5, 2002

Finally! That's exactly what I wanted! Thank you very much!

Vlip

sbendbuckeye · Aug 18, 2004

Good Thread! Thanks!

sbendbuckeye · Aug 19, 2004

Your code posted above was excellent and was just what I needed. Is there a way to tell whether a given DSN is a User, System or File DSN using API calls? Thanks!

Legacy 98055 · Feb 4, 2010

Code:

Option Explicit

Private Declare Function SQLDataSources Lib "odbc32.dll" (ByVal hEnv As Long, ByVal fDirection As Integer, ByVal szDSN As String, ByVal cbDSNMax As Integer, pcbDSN As Integer, ByVal szDescription As String, ByVal cbDescriptionMax As Integer, pcbDescription As Integer) As Long
Private Declare Function SQLAllocHandle Lib "odbc32.dll" (ByVal HandleType As Integer, ByVal InputHandle As Long, OutputHandlePtr As Long) As Long
Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" (ByVal EnvironmentHandle As Long, ByVal dwAttribute As Long, ByVal ValuePtr As Long, ByVal StringLen As Long) As Long
Private Declare Function SQLFreeHandle Lib "odbc32.dll" (ByVal HandleType As Integer, ByVal Handle As Long) As Long

Private Const DSN_LEN As Long = 32
Private Const DESC_LENGTH As Long = 128
Private Const FoundOk As Long = 0
Private Const GetNext As Long = 1
Private Const Null_HANDLE As Long = 0
Private Const HANDLE_ENV As Long = 1
Private Const ATTR_ODBC_VERSION As Long = 200
Private Const OV_ODBC3 As Long = 3
Private Const IS_INTEGER As Long = (-6)

Sub Example()
    If DSNExists("MS Access Database") Then
        MsgBox "DSN Exists"
    Else
        MsgBox "DSN does not exist"
    End If
End Sub

Function DSNExists(DSN As String) As Boolean

 Dim hEnv As Long
 Dim sServer As String
 Dim sDriver As String
 Dim nSvrLen As Integer
 Dim nDvrLen As Integer
 
 If SQLAllocHandle(HANDLE_ENV, _
     Null_HANDLE, hEnv) <> 0 Then
    
     If SQLSetEnvAttr(hEnv, _
         ATTR_ODBC_VERSION, _
         OV_ODBC3, _
         IS_INTEGER) <> 0 Then
        
         sServer = Space$(DSN_LEN)
         sDriver = Space$(DESC_LENGTH)
        
         Do While SQLDataSources(hEnv, _
             GetNext, _
             sServer, _
             DSN_LEN, _
             nSvrLen, _
             sDriver, _
             DESC_LENGTH, _
             nDvrLen) = FoundOk
             If Trim(DSN) = Trim(Left$(sServer, nSvrLen)) Then
                DSNExists = True
                Exit Do
             End If
             sServer = Space$(DSN_LEN)
         Loop
     End If
     Call SQLFreeHandle(HANDLE_ENV, hEnv)
 End If
End Function

API call to get List of ODBC DSNs

Vlip

New Member

Vlip

New Member

Excel Facts

Jack in the UK

Well-known Member

jsheehan

New Member

Jack in the UK

Well-known Member

Legacy 98055

Guest

Legacy 98055

Guest

Vlip

New Member

sbendbuckeye

Active Member

sbendbuckeye

Active Member

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

API call to get List of ODBC DSNs

New Member

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Legacy 98055

Guest

Legacy 98055

Guest

New Member

Active Member

Active Member

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock