PRB: ADO Recordset Open Method May Behave Synchronously Even if adAsyncFetch is Specified

ID: Q224332

The information in this article applies to:

SYMPTOMS

You may see the following symptoms, even though you've specified adAsyncFetch:

  1. The FetchComplete event is never raised.


  2. When stepping through the code, the line after the call to the Open method is not reached until the Open method call has completed.



CAUSE

When a recordset is opened with adAsyncFetch, only those records that are not returned during the initial fetch of records are asynchronously retrieved. If all of the records are returned in the initial fetch, no asynchronous fetching occurs, and the FetchComplete event is never raised.


RESOLUTION

Set the recordset's "Initial Fetch Size" property to a low value, such as 1.


STATUS

This behavior is by design.


MORE INFORMATION

When you specify adAsyncFetch, 50 rows are fetched by default in the first batch of records. However, you can control the number of records in the initial fetch by setting the "Initial Fetch Size" property in the ADO Recordset Properties collection.

You must set the "Initial Fetch Size" property after the ADO recordset object is created, but before the recordset is opened. Also, because asynchronous fetching requires client-size cursors, be sure to set "Initial Fetch Size" after the code to set the recordset's CursorLocation to adUseClient.

Steps to Reproduce Behavior

In the following example, the default Initial Fetch Size prevents the FetchComplete event from being raised. To work around the problem, set the Initial Fetch Size to 1, so that most records are asynchronously fetched and the FetchComplete event is raised

This example uses the Publishers table in the Pubs sample database that comes with Microsoft SQL Server versions 6.5 and 7.0.
  1. In Microsoft Visual Basic 6.0, create a new Standard EXE Project. Form1 is created by default.


  2. Add a reference to either the Microsoft ActiveX Data Objects 2.0 Library or the 2.1 Library.


  3. Copy the example code below into Form1's code window.


  4. In the connect string (strCnn), replace "MyServer" with the name of your server.


  5. Run the project and note that FetchComplete is not raised (you do not see the message box). Close the app.


  6. Uncomment the following line of code:


    7. 
'rst.Properties("Initial Fetch Size") = 1
  7. Run the project and note that FetchComplete is now raised.



Private WithEvents rst As ADODB.Recordset

Private Sub Form_Load()

   Dim strCnn As String

   strCnn = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=Pubs;User ID=sa;Password="

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseClient

   'Uncomment the next line to workaround the problem
   'rst.Properties("Initial Fetch Size") = 1

   rst.Open "select * from publishers", strCnn, adOpenKeyset, adLockOptimistic, adAsyncFetch
   rst.MoveLast

End Sub

Private Sub rst_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

   MsgBox "FetchComplete executed"

End Sub


REFERENCES

For more information on the Initial Fetch Size property, please refer to the example provided in the following article:

"What's New in ADO 2.0."

For additional information on the adAsyncFetch flag, please refer to the Microsoft ADO Programmer's Reference, under "Open Method (ADO Recordset)."

Additional query words: 


Keywords          : kbADO kbDatabase kbGrpVBDB 
Version           : WINDOWS:2.0,2.01,2.1
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 12, 1999