ADODB Connection Timeout

If you are doing multi-threaded application or web portal, it may be possible to experience high frequency of ADODB connection timeout.

The reason is because there isn't enough database resources to service your needs.

Refer to 'Pooling in the Microsoft Data Access Components'

The answer is to make sure that each ADODB.connection is properly closed thereafter. You may think that you know the answer, but actually it can be tricky.

The rules are:

1.) Close all opened ADODB.connection object.

If you open a Connection object, remember to close it. Do not rely on garbage collection to implicitly close your connections.

2.) A disconnected ADODB.Recordset object has not actually been disconnected. If you don't set the 'ActiveConnection' property of the ADODB.Recordset to Nothing/NULL, it is not disconnected and the connection is not released into the pool.

Public Function ServiceTest(ConnectStr As String, _
                            SqlText As String) _
                            As String
 
   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
 
   cnn.ConnectionString = ConnectStr
   cnn.CursorLocation = adUseClient
   cnn.Open
   Set rst.ActiveConnection = cnn
   rst.Open SqlText, , adOpenKeyset, adLockBatchOptimistic, -1
   
   Set rst.ActiveConnection = Nothing 'This is important
   rst.Close
   Set rst = Nothing

   cnn.Close   'This is important
   Set cnn = Nothing
 
   Exit Function

3.) Similar to setting 'ActiveConnection' property of the ADODB.Recordset to Nothing/NULL, same applies to ADODB.Command object. You have to set the 'ActiveConnection' property of the ADODB.Command to Nothing/NULL.

Public Function ServiceTest(ConnectStr As String, _
                            SqlText As String) _
                            As String

   Dim lngRowsAffected as integer
   Dim cmdobj as new ADOD.command
   Dim cnn As New ADODB.Connection 
   Dim rst As ADODB.Recordset

   cnn.ConnectionString = ConnectStr
   cnn.CursorLocation = adUseClient
   cnn.Open

   Set cmdobj.ActiveConnection = cnn
   cmdobj.CommandType = adCmdText
   cmdobj.CommandText = SqlText 
   Set rst  = cmdobj.execute(lngRowsAffected)
   If lngRowsAffected <= 0 Then 
      'Fail
   else
      'Success
   end if

   Set rst = nothing 

   Set cmdobj.ActiveConnection = Nothing 'This is important
   Set cmdobj = Nothing

   cnn.Close  'This is important
   Set cnn = Nothing
 
   End Function

4.) Turn off 'OLE DB resource pooling'.

You can do this by adding "OLE DB Services = -2" to the ADO Connection string

http://support.microsoft.com/kb/229564

The potential delays or disadvantages of not using pooling are related to the followings.

Creating a new object, setting properties, and establishing a new connection to the database.

Which means that there isn't much bottleneck from higher level point of view.

For my case, I didn't have to turn off 'OLE DB resource pooling' where by adhering to rules no.1, 2, 3, it solved the issue.