Hi Bonnie,
I use ADO to query SQL database which contains many records.
Can I show the progress in status bar like using querytables ?
Yes you can do this if you execute your query asynchronously. But you'll
have to sink the ADO Recordset's events using a class module. When opening
your recordset, you need to make sure you use a client-side cursor, and you
must pass in adAsyncFetch to the Options argument. This will allow for
asynchronous fetching of records.
There are FetchProgress and FetchComplete events for the Recordset object
that you can utilize to update the statusbar or your progress bar. Here's
some example code that should get you started:
'/STANDARD MODULE
'/--------------------------
Private mcRCStatus As CrsStatus
Dim mcn As ADODB.Connection
Dim mrs As ADODB.Recordset
Sub test()
Dim sSQL As String
Set mcRCStatus = New CrsStatus
Set mcn = New ADODB.Connection
mcn.Open "DSN=MyDSN"
Set mrs = New ADODB.Recordset
With mrs
.CursorLocation = adUseClient
sSQL = "SELECT COUNT(ProductID) FROM dbo.tblProducts"
.Open sSQL, mcn, adOpenForwardOnly, adLockReadOnly
If Not (.BOF And .EOF) Then
mcRCStatus.NumRecords = .Fields(0).Value
End If
.Close
Set mcRCStatus.rs = mrs
sSQL = "SELECT ProductID, ProductName FROM dbo.tblProducts"
.CursorLocation = adUseClient
.Open sSQL, mcn, adOpenForwardOnly, adLockReadOnly, _
adAsyncFetch Or adCmdText
End With
End Sub
Sub RSDone()
With mrs
If .State = adStateOpen Then
If Not (.EOF And .BOF) Then
Cells(1, 1).CopyFromRecordset mrs
End If
.Close
End If
End With
Set mrs = Nothing
mcn.Close
Set mcn = Nothing
End Sub
'/CLASS MODULE (name=CrsStatus)
'/---------------------------------------
Public WithEvents rs As ADODB.Recordset
Public NumRecords As Long
Private Sub rs_FetchComplete(ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Application.StatusBar = False
RSDone
End Sub
Private Sub rs_FetchProgress(ByVal Progress As Long, _
ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, _
ByVal pRecordset As ADODB.Recordset)
Application.StatusBar = CStr(Progress) & " records of " & _
CStr(NumRecords) & " retrieved (" & Format$(Progress / NumRecords, _
"0%") & ")"
End Sub
NOTES: I thought at first that I could use the MaxProgress parameter in the
FetchProgress event routine for the total records so that I could calculate
progress %. But this number incremented as new rows were fetched as well
(it stayed a bit ahead of Progress, but the end effect was that the %
complete slowed considerably as it approached 100%). Maybe if you have an
optimized query (uses index seek/scan instead of table scan), this number
will be accurate from the very beginning. Anyway, to avoid using
MaxProgress, I executed a query to get the recordcount (should be pretty
fast) and passed that value to the class before executing the "real" query.
MORE NOTES: You may want to trap for the user cancelling execution (see
Application.EnableCancelKey in help) so you can clean up your recordset and
connection objects.
Hope this helps!
