Listing DSN's on a machine in VB

[HoustonGuy]

I want my app to list all DSN's on the machine it is running on. How do I do this?

I know how to connect to it in ADO once a DSN is chosen but I need to know how to dynamically retrieve the list at runtime and list the tables available from the DSN they choose as well.

Thanks for the help. Cody ford
Data Mining, Cleansing and Reporting Specialist
codyjford@hotmail.com

SQL, VB6, Seagate Info/Crystal Reports

 

[dbljackson]

I found this and it seems to do the job.

'Declarations
Private Declare Function SQLAllocEnv Lib "odbc32.dll" _
(phenv As Long) As Integer

Private Declare Function SQLDataSources Lib "odbc32.dll" _
(ByVal hEnv As Long, ByVal fDirection As Integer, _
ByVal szDSN$, ByVal cbDSNMax%, pcbDSN As Integer, _
ByVal szDescription As String, _
ByVal cbDescriptionMax As Integer, _
pcbDescription As Integer) As Integer

Private Declare Function SQLFreeEnv Lib "odbc32.dll" _
(ByVal hEnv As Long) As Integer

Private Const SQL_SUCCESS As Long = 0
Private Const SQL_FETCH_NEXT = 1
Private Const SQL_FETCH_FIRST_SYSTEM = 32


'Code to load
Function LoadLstDSN()

Dim iRet As Integer
Dim sDSN As String
Dim sDriver As String
Dim iDSNLen As Integer
Dim iDriverLen As Integer
ReDim DSNArray(0) As String
Dim lEnvHandle As Long

iRet = SQLAllocEnv(lEnvHandle)
sDSN = Space(35)
sDriver = Space(1024)
iRet = SQLDataSources(lEnvHandle, SQL_FETCH_FIRST_SYSTEM, _
sDSN, 1024, iDSNLen, sDriver, 1024, iDriverLen)

If iRet = SQL_SUCCESS Then

sDSN = Mid(sDSN, 1, iDSNLen)
sDriver = Mid(sDriver, 1, iDriverLen)
DSNArray(0) = sDSN & " | " & sDriver
lstDSNs.AddItem sDSN & Chr(9) & sDriver

Do Until iRet <> SQL_SUCCESS
sDSN = Space(35)
sDriver = Space(1024)
iRet = SQLDataSources(lEnvHandle, SQL_FETCH_NEXT, _
sDSN, 1024, iDSNLen, sDriver, 1024, iDriverLen)


If Trim(sDSN) <> &quot;&quot; Then
sDSN = Mid(sDSN, 1, iDSNLen)
sDriver = Mid(sDriver, 1, iDriverLen)
ReDim Preserve DSNArray(UBound(DSNArray) + 1)
DSNArray(UBound(DSNArray)) = sDSN & &quot; | &quot; & sDriver
End If
Loop

End If

iRet = SQLFreeEnv(lEnvHandle)


End Function