How To Implement Session Pooling from Visual Basic ADO Program

This article has been archived. It is offered "as is" and will no longer be updated.
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


Note You must change uid=<username> and pwd=<strong password> to the appropriate username and password before you run this code. Make sure that uid has the appropriate permissions to perform the required operations on the specified database.
'Global connect handle declarationDim conn1 As New ADODB.ConnectionPrivate 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=<username>;pwd=<strong password>;"    For i = 0 To 10        conn.Open "dsn=pubs;uid=<username>;pwd=<strong password>;"        rs.Open "select * from authors", conn        rs.Close        conn.Close        Set rs = Nothing        Set conn = Nothing    Next    conn1.Close    Set conn1 = NothingEnd 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.

Specifying "OLE DB Services=-1;" on the connection string will also use session pooling. If this is set on the connection string it overrides what is set in the registry.
References
For additional information, visit the following Microsoft Developer Network (MSDN) Web site:
Properties

Article ID: 228843 - Last Review: 01/11/2015 02:41:10 - Revision: 5.0

Microsoft Data Access Components 2.1, Microsoft Data Access Components 2.5, Microsoft Data Access Components 2.6, Microsoft Data Access Components 2.7

  • kbnosurvey kbarchive kbproductlink kbhowto kbregistry KB228843
Feedback