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

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.

NOTE: The FetchComplete and FetchProgress events fire without error in Microsoft Data Access Components version 2.7.

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.

If you are using a dual processor you then also need to set the "Background Fetch Size" to a low value such as 1.

This problem was corrected in ADO 2.6.

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:

   'rst.Properties("Initial Fetch Size") = 1
   					

7. Run the project and note that FetchComplete is now raised.

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." (http://msdn.microsoft.com/library/techart/msdn_newado20.htm)

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