Progress Bar with SQL query

How to make a progress bar during running of SQL statement.

I have a big database file, and i make a search by using SQL statment with dataControl.recordsource, how to know the progressing of search.

Who is Participating?
TimCotteeHead of Software ServicesCommented:
Azra is right, the key to getting this to work properly is using adUseClient for the cursor location. Here is an example of how this can be used:

Private WithEvents rstLogInfo As ADODB.Recordset
Private WithEvents cnnLive As ADODB.Connection
Private lngMax As Long

Private Sub Command1_Click()
    lngMax = 500000
    Set cnnLive = New ADODB.Connection
    Set rstLogInfo = New ADODB.Recordset
    cnnLive.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Live;Data Source=OSIUK009"
    With rstLogInfo
        .CursorLocation = adUseClient
        pgbFetch.Max = lngMax
        .Open "Select * From LotBaseLog", cnnLive, adOpenStatic, adLockReadOnly, adAsyncFetch
    End With
End Sub
Private Sub Command2_Click()
    Set rstLogInfo = Nothing
    Set cnnLive = Nothing
End Sub

Private Sub rstLogInfo_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    MsgBox "Complete"
End Sub

Private Sub rstLogInfo_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    lblFetch.Caption = Progress & " / " & lngMax
    pgbFetch.Value = Progress
End Sub

There are a number of things to remember when doing this sort of thing. One is that you need to fully manage the lifetime of the asynchronous call and ensure that you cancel it and set the object to nothing in order to release the resources. The important thing to remember though is that this is all irrelevant when doing a select statement; the reason being that until you have completed the select you don't know how many records will be returned, I have used an expected value of 500K in this example as I know that the table in question has approximately this number of rows. The Progress and MaxProgress values returned in the event are only related to the approximate number of rows processed so far and as such cannot be used as an accurate guide to progress. You CAN use this to indicate that some progress is continuing but NOT to determine accurately the extent of that progress. Another thing to remember is that you cannot access these events from the data control though I am not sure from memory whether the ado data control will allow these events.

I hope this helps (explain if not solve) the situation.
Éric MoreauSenior .Net ConsultantCommented:
You can't correctly use a progress bar with a Select statement. The reason is that you don't have events triggered that informs you the percentage retreived.
>>how to know the progressing of search.

Guess wildly.  That is my best suggestion.  emoreau is right.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

if you use the ActiveX Data Objects, and declare a recordset object WithEvents, it has a FetchProgress event which gives you an approximation of an operation.  it may be of interest to you...
AzraSound, I've tried using that and I couldn't get it to work properly. I might have been doing something wrong, I would be interested if you could explain a bit further.
To get Events, you might need to Specify that your CONNECTION is asynchronous, eg:

Dim WithEvents RS as RecordSet
Dim WithEvents CN As Connection

    CN.ConnectionString = ...

    CN.Open , , , adAsyncConnect


    RS.Open CMD, , adOpenStatic, , adAsyncExecute

Then use the VBIDE to see what Events are available and (eg) add debug statements to all of them so that you can see what is going on.

This way, use RS instead of dataControl.recordsource.  Then when the recordset has loaded (RS_FetchComplete?) simply: Set DataControl.RecordSource = RS

Another thought on this is to find out how long the command will take before running it - depends on the server, but on DB2 if you specify a CommandTimeout of zero (0), then it will return an error saying the specified command's estimated query processing time of xxx exceeds the maximum of 0.  (or words to that effect).

So parse that result to get the estimated query processing time, set a progress bar to that value then run a timer with one second intervals to update the progress bar.

Bear in mind that (I believe), Timers don't work Asyncronously if the program is run from the VBIDE, but they do probably work fine when run from a compiled exe - if not, then use SetTimer and KillTimer which should work as required (but it also depends on the ODBC being used).

Private Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long
Thanks for the wonderful explanation Tim, I'll try it when I get time.
Interesting.  Didn't know this was available.
HosamElzohryAuthor Commented:
Thanks for All, special thanks to AzraSound
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.