Article ID: 237844 - Last Review: February 15, 2007 - Revision: 4.2

How To Enable ODBC Connection Pooling in a Visual Basic ADO Application

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q237844
Expand all | Collapse all

SUMMARY

By default, ADO uses OLEDB session pooling to maintain a pool of connections to the database. In some cases, you might want to use ODBC connection pooling instead of OLEDB session pooling. This article describes what is necessary to enable ODBC connection pooling from a Visual Basic/ADO application.

Note The following only applies if you are using an ODBC driver to establish the connection to your database.

MORE INFORMATION

To enable ODBC connection pooling from a Visual Basic/ADO application, there are two necessary steps:
  1. Open the ODBC Data Source Administrator from the control panel. Select the Connection Pooling tab. Find the driver that you are using in the list and double-click on it. Choose the option Pool connections to this driver and enter a timeout value.

    Note This only applies to the ODBC Administrator version 3.5 or later. If you are using an earlier version of the Administrator, then you need to find the CPTimeout value for your driver in the registry under the following registry key and set the value:
    HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI/Driver_Name
    For more information on setting this value in the registry, please see the following document:
    http://msdn2.microsoft.com/en-us/library/ms810829.aspx (http://msdn2.microsoft.com/en-us/library/ms810829.aspx)
    The second step is to add an ODBC API function call to SQLSetEnvAttr in your application with the appropriate options to enable ODBC connection pooling for the process. This function should only be called once per process and must be called prior to executing any ADO code. Below are the steps necessary to create a complete Visual Basic code sample demonstrating this:
    1. Create a new Visual Basic Standard EXE project. Form1 is created by default.
    2. From the Project menu, choose References and add a reference to Microsoft ActiveX Data Objects.
    3. On the default form, add a CommandButton.
    4. Cut and paste the following code into the form. You need to modify the connection string so that it connects to your database:

      Note You must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
      Option Explicit
Dim rc As Long

Const dbconnstring = "DSN=<Your DSN>;uid=<User ID>;pwd=<Strong Password>;OLE DB Services=-2"

Const SQL_ATTR_CONNECTION_POOLING = 201
Const SQL_CP_ONE_PER_DRIVER = 1
Const SQL_IS_INTEGER = -6
Const SQL_CP_OFF = 0

Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" ( _
                    ByVal EnvironmentHandle As Long, _
                    ByVal EnvAttribute As Long, _
                    ByVal ValuePtr As Long, _
                    ByVal StringLength As Long) As Integer
                    
Private Sub Command1_Click()
    
    Dim SQL As String
   
    'Test connection pooling
    Dim i As Long
    For i = 1 To 10
        Dim cn As ADODB.Connection
        Set cn = New ADODB.Connection
        cn.Open dbconnstring
        cn.Close
        Set cn = Nothing
    Next
        
    MsgBox "Connection finished"

End Sub

Private Sub Form_Load()
    
    'Enable connection pooling ..  this must be done before any ADO calls
    'are made.  Only needs to occur one time per process
    rc = SQLSetEnvAttr(0&, _
                 SQL_ATTR_CONNECTION_POOLING, _
                 SQL_CP_ONE_PER_DRIVER, _
                 SQL_IS_INTEGER)
    If rc <> 0 Then
        Debug.Print "SQLSetEnvAttr Error " & rc
    End If

End Sub

Private Sub Form_Unload(Cancel As Integer)
        
    Call SQLSetEnvAttr(0&, _
                SQL_ATTR_CONNECTION_POOLING, _
                SQL_CP_OFF, _
                SQL_IS_INTEGER)

End Sub
    5. Compile the project into an EXE. Before running the compiled EXE, you might want to use some utility to monitor the connections being made to the database. With SQL Server 7.0, you can use Profiler (SQL Trace with SQL Server 6.5). Run this program and you should see only one connection made to the database. Each subsequent connection utilizes the existing connection in the pool.
APPLIES TO
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
Back to the top
Keywords: 
kbhowto KB237844