Fetch Progress using ADODB.Recordset
bor@t
I am currently working on a VB program that writes/reads data from an SQL
7.0 Database.
At one point in the program I open a recordset from a large table - and the
program stops responding whilst the '.Open ' command is executing, and then
'comes alive' again when the RS has opened.
To solve this problem I have been investigating the FetchComplete and the
FetchProgress statements, but I can't get it to work. The FetchComplete is
never called, and the FetchProgress is only called once - irrespective of
Initial Fetch Size & Background Fetch Size.
I am using ADO 2.6 (have also tried 2.1 & 2.5)
Current code snippets:
Dim objConn As New ADODB.Connection
Dim WithEvents objRS As ADODB.Recordset
------------------------------------------------------------------
Set objRS = New ADODB.Recordset
objRS.CursorLocation = adUseClient
objRS.CacheSize = 1
objRS.Properties("Initial Fetch Size") = 0
objRS.Properties("Background Fetch Size") = 25
objRS.Open "SELECT DISTINCT [Date] FROM Traffic WHERE [Carrier] = 'C'",
objConn, adOpenStatic, adLockBatchOptimistic, adAsyncFetch
' After calling the above "objRS.Open", the machine freezes whilst waiting
for the recordset to open, then simply
' Debug.Prints the FetchProgress text (*only once*) and then continues with
the code. I have tried changing all the Fetch Size values
' and the CacheSize value to no avail.
------------------------------------------------------------------
Private Sub objRS_FetchComplete(ByVal pError As ADODB.Error, adStatus As
ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Debug.Print "Fetch Complete"
DoEvents
End Sub
------------------------------------------------------------------
Private Sub objRS_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As
Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As
ADODB.Recordset)
Debug.Print cStr(Progress) & ":" & cStr(MaxProgress)
DoEvents
End Sub
------------------------------------------------------------------
Anyone have any ideas?
Regards,
bor@t