Visual Basic System Services
SQLDataSources: Retrieve ODBC DSN User and System Connections
Posted:   Tuesday May 01, 2001
Updated:   Monday December 26, 2011
Applies to:   VB4-32, VB5, VB6
Developed with:   VB6, Windows 2000
OS restrictions:   None
Author:   VBnet - Randy Birch


Rundll32: Control Panel Functions O-R
GetFileVersionInfo: Retrieve MS ODBC Driver Information
SQLAllocHandle is an ODBC 3.0 API, but the MSDN states it degrades internally into the ODBC 1 & 2 - compliant SQLAllocEnv.

Instead of enumerating the registry in order to obtain defined ODBC DSN connections available on a system, this method uses the SQLAllocHandle API to enumerate the data.

The MSDN states that SQLAllocHandle is deprecated and mapped by the ODBC 3.x Driver Manager to guarantee backward compatibility of ODBC 3.x drivers that are used with ODBC 2.x applications. The Driver Manager performs this mapping regardless of the version of the application.

The demo retrieves the ODBC User and System DSN connection data using the SQLDataSources API, after obtaining a handle to the environment via SQLAllocHandle and setting the environment state via SQLSetEnvAttr. Returned in the SQLDataSources call are both the DSN connection name and the DSN driver description, which are used to populate separate lists. This code is easily modified to populate other controls, such as a listview or menu, to allow user-selection of existing DSN connections.

 BAS Module Code

 Form Code
To a form add two list boxes (List1 and List2) along with the following code:

Option Explicit
' Copyright 1996-2011 VBnet/Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
' Distribution: You can freely use this code in your own
'               applications, but you may not reproduce 
'               or publish this code on any web site,
'               online service, or distribute as source 
'               on any media without express permission.
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()


End Sub

Private Sub GetUserSystemDSN()

   Dim hEnv As Long         'handle to the environment
   Dim sServer As String
   Dim sDriver As String
   Dim nSvrLen As Integer
   Dim nDvrLen As Integer
  '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
           'add data to the controls
            List1.AddItem Left$(sServer, nSvrLen)
            List2.AddItem Left$(sDriver, nDvrLen)
           'repad the strings
            sServer = Space$(SQL_MAX_DSN_LENGTH)
            sDriver = Space$(SQL_MAX_DESC_LENGTH)
      End If  'If SQLSetEnvAttr
     'clean up
      Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)
   End If  'If SQLAllocHandle
  'since each DSN returned its corresponding
  'driver, and a given driver can be used
  'for multiple DSN's, remove any adjacent
   RemoveListDuplicates List2

End Sub

Private Sub RemoveListDuplicates(lst As ListBox)

   Dim n As Long

   If TypeOf lst Is ListBox Then
      With lst
         If .ListCount > 1 Then
            For n = .ListCount - 1 To 0 Step -1
               If .List(n) = .List(n - 1) Then lst.RemoveItem n
         End If
      End With
   End If
End Sub


PayPal Link
Make payments with PayPal - it's fast, free and secure!


Copyright 1996-2011 VBnet and Randy Birch. All Rights Reserved.
Terms of Use  |  Your Privacy


Hit Counter