How to list ODBC Data Sources installed on workstation? 1

[AMadden]

I'm trying to write a VB6 or Access 2000 VBA program that will list the ODBC data sources installed on a users workstaiton.

Further, I want to list only data sources of a specific type, such as Microsoft SQL Server, etc.

I know how to view the information using the ODBC Administrators tools, but I have need to return the information programmatically. Does this have to be donw with an API call? ...or is there a simpler way? Any direction on this would be appreciated.

Thanks.

-Art Madden

 

[kevinclark]

In VB apps I use the SQLDataSources and SQLAllocEnv APIs. I have pasted a cut down version of using them.


paste the following code into a basModule
---------------------------------------------------------
Option Explicit
Option Compare Text

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 Integer
Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (env As Long) As Integer

Public Function DSNList(DB_Driver_Name As String)
Dim dSource As Integer
Dim strDSN As String * 1024
Dim strDRV As String * 1024
Dim szDSN As Integer
Dim szDRV As Integer
Dim hdlENV As Long
On Error Resume Next
If SQLAllocEnv(hdlENV) <> -1 Then
Do Until dSource <> 0
dSource = SQLDataSources(hdlENV, 1, strDSN, 1024, szDSN, strDRV, 1024, szDRV)
If Left$(strDRV, szDRV) = DB_Driver_Name Then
MsgBox Left$(strDSN, szDSN)
End If
Loop
End If
End Function
---------------------------------------------------------



Paste following code into From Module and Add one
Command Button (name it Command1) to the Form
---------------------------------------------------------
Private Sub Command1_Click()
DSNList &quot;Sybase SQL Anywhere 5.0&quot;
End Sub
---------------------------------------------------------

 

[AMadden]

Many thanks Kevin. It worked first time!

-Art M.