EnumDSNs


Description:
This function encapsulates the API code necessary to enumerate through the DSNs and puts them in a collection. Usage should be straightforward enough, see the sample code.
 
Code:
Public Enum enumDSNTypes
    dsn_User = 31
    dsn_System = 32
End Enum

Private Const SQL_FETCH_NEXT = 1
Private Declare Function SQLGetInstalledDrivers Lib "ODBCCP32.DLL" _
   (ByVal s As String, ByVal sl As Integer, ByRef so As Integer) As Long
   
Private Declare Function SQLFreeEnv Lib "ODBC32.DLL" (ByVal han As Long) _
   As Long
   
Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (ByRef han As Long) _
   As Long
   
Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal han As _
   Long, ByVal iDir As Integer, ByVal sServerName As String, ByVal _
   iBuf1 As Integer, ByRef iRealBuf1 As Integer, ByVal sDesc As String, _
   ByVal iBuf2 As Integer, ByRef iRealBuf2 As Integer) As Long
                
Public Function EnumDSNs(DSNList As Collection, _
   Optional dsnType As enumDSNTypes = dsn_System)

    Dim sDSN As String
    Dim sDesc As String
    
    Dim iLenDSN As Integer
    Dim iLenDesc As Integer
    Dim nRet As Long
    Dim hSQL As Long
    
    Dim bContinue As Boolean
    
    bContinue = False

    SQLAllocEnv hSQL
    
    Do
        sDSN = Space(1024)
        sDesc = Space(1024)
        
        If bContinue Then
            nRet = SQLDataSources(hSQL, SQL_FETCH_NEXT, sDSN, _
                   1024, iLenDSN, sDesc, 1024, iLenDesc)
        Else
            nRet = SQLDataSources(hSQL, dsnType, sDSN, 1024, _
                   iLenDSN, sDesc, 1024, iLenDesc)
                   
            bContinue = True
        End If
        
        If nRet = 0 Then
            DSNList.Add Mid(sDSN, 1, iLenDSN)
        End If
    
    Loop While iLenDSN <> 0 And nRet = 0
        
    SQLFreeEnv hSQL
    
End Function
 
Sample Usage:
 
    Dim colDSN As New Collection
    EnumDSNs colDSN
    
    Dim vDSNEntry
    For Each vDSNEntry In colDSN
        Debug.Print vDSNEntry
    Next