Asynchronously Fetching Results

With large result sets, fetching can take a long time. With asynchronous fetching, the user can be shown some results almost immediately and then the rest can be retrieved in the background.

Asynchronous fetching in ADO is implemented only for client cursors (CursorLocation = adUseClient). The actual background fetching is implemented by using the Remote Data Service (RDS) cursor engine.

There are two types of asynchronous fetches, blocking and nonblocking. The difference between blocking and nonblocking is in how operations that would attempt to refer to unfetched rows are handled. In the blocking case, the function being requested (for example, MoveLast) is executed and, before returning control to the application, ADO waits until the last row has been made available. In a nonblocking scenario, the MoveLast operation returns immediately but the current row is changed to the last one fetched; the user would be placed only on the last row currently fetched, not on the last row that would eventually be fetched into the result set. As a result, the user can see that row and all of the other rows already fetched.

Note The distinction between blocking and nonblocking asynchronous fetches is subtle, and there are uses for both cases.

By default, 50 rows are fetched before control is returned to the developer. The value 50 was chosen to represent approximately what might be displayed in a grid on a form. This number can be controlled by using the Initial Fetch Size property in the Recordset Properties collection.

Asynchronous fetching can be requested only by using the Recordset object, through the Open method's Options parameter. This is demonstrated in the following example, which uses a blocking (adAsyncFetch) asynchronous fetch.

Note This example shows the use of only one of the notifications available in ADO—FetchComplete—and there are several others. To learn more, see the ADO documentation.

Dim WithEvents rst As ADODB.Recordset

Sub Form_Load()

   Dim conn as New ADODB.Connection
   Set Rst = New ADODB.Recordset

   conn.ConnectionString = _
   "Provider=SQLOLEDB;Data Source=sql70server;" _
      & "User ID=sa;Password='';Initial Catalog=pubs"
   conn.Open

   Set rst.ActiveConnection = conn
   rst.CursorLocation = adUseClient

   rst.Properties("Initial Fetch Size") = 1
   rst.Open "select * from authors", , , , adAsyncFetch
   Debug.Print rst.Fields(0) & "   : First field, Fetching Started"

End Sub

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

   Debug.Print "Completed Fetching Results."

End Sub