Progress Bar with SQL query

Posted on 2001-07-03
Last Modified: 2008-02-01
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.

Question by:elzohry
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
LVL 70

Expert Comment

by:Éric Moreau
ID: 6250749
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.
LVL 38

Expert Comment

ID: 6250829
>>how to know the progressing of search.

Guess wildly.  That is my best suggestion.  emoreau is right.
LVL 28

Expert Comment

ID: 6250916
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...
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Expert Comment

ID: 6251659
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.

Expert Comment

ID: 6251747
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

LVL 43

Accepted Solution

TimCottee earned 100 total points
ID: 6251879
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.

Expert Comment

ID: 6252320
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

Expert Comment

ID: 6252331
Thanks for the wonderful explanation Tim, I'll try it when I get time.
LVL 38

Expert Comment

ID: 6252828
Interesting.  Didn't know this was available.

Author Comment

ID: 6255180
Thanks for All, special thanks to AzraSound

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question