Courtesy of Randy Birch

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 SQL_MAX_DSN_LENGTH As Long = 32
Private Const SQL_MAX_DESC_LENGTH As Long = 128
Private Const SQL_SUCCESS As Long = 0
Private Const SQL_FETCH_NEXT As Long = 1
Private Const SQL_NULL_HANDLE As Long = 0
Private Const SQL_HANDLE_ENV As Long = 1
Private Const SQL_ATTR_ODBC_VERSION As Long = 200
Private Const SQL_OV_ODBC3 As Long = 3
Private Const SQL_IS_INTEGER As Long = (-6)

Private Sub GetUserSystemDSN()
Dim hEnv As Long
Dim sServer As String
Dim sDriver As String
Dim nSvrLen As Integer
Dim nDvrLen As Integer
Dim lstDSNs As String

If SQLAllocHandle(SQL_HANDLE_ENV, _
SQL_NULL_HANDLE, hEnv) < 0 Then

If SQLSetEnvAttr(hEnv, _
SQL_ATTR_ODBC_VERSION, _
SQL_OV_ODBC3, _
SQL_IS_INTEGER) < 0 Then

sServer = Space$(SQL_MAX_DSN_LENGTH)

Do While SQLDataSources(hEnv, _
SQL_FETCH_NEXT, _
sServer, _
SQL_MAX_DSN_LENGTH, _
nSvrLen, _
sDriver, _
SQL_MAX_DESC_LENGTH, _
nDvrLen) = SQL_SUCCESS

lstDSNs = lstDSNs & Left$(sServer, nSvrLen) & vbNewLine

sServer = Space$(SQL_MAX_DSN_LENGTH)

Loop

End If 'If SQLSetEnvAttr

Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)

End If 'If SQLAllocHandle

MsgBox lstDSNs

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"XP" wrote in message
...
Using Windows XP and Office 2003;

If you have example code on how to loop thru or enumerate the DSN's on
your
local machine could you please post it?

Thanks much in advance.