HOWTO: Implement Session Pooling from Visual Basic ADO Program

ID: Q228843

The information in this article applies to:

Microsoft Data Access Components versions 2.0, 2.1, 2.5

SUMMARY

Session pooling can provide high performance in an environment that exhibits frequent connects and disconnects from a backend database. This article describes how to get session pooling in a Visual Basic program that uses ActiveX Data Objects (ADO) component to talk with the backend database.

MORE INFORMATION

The following Visual Basic program exhibits how you can do session pooling. The trick to doing this is to make sure that one reference to a connection object is retained at global scope in the Visual Basic application. This keeps a reference to the IDataInitialize interface, which is the OLE DB Service Components where session pooling occurs.

Sample Code


'Global connect handle declaration
Dim conn1 As New ADODB.Connection

Private Sub Form_Load()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer

'Do not uncomment the following. Let time to be default, that is, never 
'expire conn1.CommandTimeout = 60 is not enough to keep connection alive.

    conn1.Open "dsn=pubs;uid=sa;pwd=;"

    For i = 0 To 10
        conn.Open "dsn=pubs;uid=sa;pwd=;"
        rs.Open "select * from authors", conn
        rs.Close
        conn.Close
        Set rs = Nothing
        Set conn = Nothing
    Next

    conn1.Close
    Set conn1 = Nothing

End Sub

The preceding ADO program uses the default "MSDASQL" (Microsoft OLEDB Provider for ODBC drivers). To get the required session pooling with this provider you have to change or add the following registry entry:

HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}

Look for the value "OLEDB_SERVICES." This is a hex value, and should be set to 0xffffffff to enable ADO session pooling.

If you want to use "SQLOLEDB", the native OLEDB Provider for SQL Server, you have to change/add the following registry entry:

HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}

Look for the value "OLEDB_SERVICES." Again, this is a hex value and should be set to 0xffffffff to enable ADO session pooling.

REFERENCES

For additional information please see the following article:

http://msdn.microsoft.com/library/techart/pooling2.htm

Additional query words:

Keywords : kbMDAC kbOLEDB kbRegistry kbGrpMDAC kbDSupport
Version : WINDOWS:2.0,2.1,2.5
Platform : WINDOWS
Issue type : kbhowto