PDA

View Full Version : [SOLVED] Visualize state of ADODB recordset in statusbar



pulsar777
11-15-2018, 04:50 AM
Hi, I have a listobject table that was created from ADODB recordset.
I'd like to dynamically update the Application.StatusBar reflecting current state like Connecting, Fetching, etc.



Sub test()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim s As String
Dim SQL As String

s = "/*someConnectionString*/"
SQL = "/*someReadCommand*/"

cn.Open s
rs.Open SQL, cn

Do While rs.State > 1
DoEvents
If CBool(rs.State And adStateExecuting) Then
Application.StatusBar = "Waiting for server...."
ElseIf CBool(rs.State And adStateConnecting) Then
Application.StatusBar = "Connecting...."
End If
Do While CBool(rs.State And adStateFetching)
DoEvents
Application.StatusBar = "Fetching results: " & rs.RecordCount
Loop
Loop
With Sheet1.ListObjects(1).QueryTable
Set .Recordset = rs
.Refresh
End With

End Sub


Instead of displaying the message in status bar on bottom left, I get to see only ADODB system info with being updated
recordCount on bottom right.

Thanks for any advice

Jan Karel Pieterse
11-15-2018, 11:37 PM
I'm afraid that cannot be done. Even though the rs.Open statement can be set to run asynchonously, it does not report progress until the fetch is completely done.

Jan Karel Pieterse
11-16-2018, 12:02 AM
I was wrong, it can be done using a class module: https://www.pcreview.co.uk/threads/can-i-show-the-progress-of-ado-query.955548/

pulsar777
11-16-2018, 12:05 PM
Great, it's working! Thanks a bunch