Binaryworld - Get List of User/System DSN on your local machine using ODBC API ... [ VB -> ODBC ]

Step-By-Step Example

- Create a standard exe project
- Add the following code in form1
- Press F5 to run the project

Click here to copy the following block

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 Form_Load()
  GetDSNList
End Sub

'//This will list both User and System DSN of local machine
Private Sub GetDSNList()

  Dim hEnv As Long                    'handle to the environment
  Dim sServer As String
  Dim sDriver As String
  Dim nSvrLen As Integer
  Dim nDvrLen As Integer

  Me.WindowState = vbMaximized
  Me.AutoRedraw = True

  'obtain a handle to the environment
  If SQLAllocHandle(SQL_HANDLE_ENV, _
      SQL_NULL_HANDLE, hEnv) <> 0 Then

    'if successful, set the
    'environment for subsequent calls
    If SQLSetEnvAttr(hEnv, _
        SQL_ATTR_ODBC_VERSION, _
        SQL_OV_ODBC3, _
        SQL_IS_INTEGER) <> 0 Then

      'set up the strings for the call
      sServer = Space$(SQL_MAX_DSN_LENGTH)
      sDriver = Space$(SQL_MAX_DESC_LENGTH)

      'load the DSN names
      Do While SQLDataSources(hEnv, _
          SQL_FETCH_NEXT, _
          sServer, _
          SQL_MAX_DSN_LENGTH, _
          nSvrLen, _
          sDriver, _
          SQL_MAX_DESC_LENGTH, _
          nDvrLen) = SQL_SUCCESS

        Me.Print "DSN Name :" & Left$(sServer, nSvrLen)
        Me.Print "Driver Name :" & Left$(sDriver, nDvrLen)
        Me.Print String(100, "-")

        Debug.Print "DSN Name :" & Left$(sServer, nSvrLen)
        Debug.Print "Driver Name :" & Left$(sDriver, nDvrLen)
        Debug.Print String(100, "-")

        'repad the strings
        sServer = Space$(SQL_MAX_DSN_LENGTH)
        sDriver = Space$(SQL_MAX_DESC_LENGTH)
      Loop
    End If

    'clean up
    Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)
  End If
End Sub