How to list DSNs installed on workstation?

[AMadden]

I would like to write a VBA routine in Access 2000 that will present a list of available ODBC data sources to a user. (These would be ODBC datasources that have been previously installed and configured on the users workstation.) In addition, I want to only show data sources of a particular type, such as Microsoft SQL Server.

Will this have to be accomplished through an API call? ...or is there a simpler way to identify the ODBC datasources? Any help apprecitated.

- Art Madden

 

[AMadden]

I cross posted this question in the VB6 Programming forum and received a response from KevinClark that did exactly what I needed. ...thought I would post his response here in case anyone else was interested. -am

----
By Kevin Clark....
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