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
  • 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...
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.


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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows 10 start screen issues 9 54
Put text in a picture ASP.NET C# 2 56
Add and format columns in vb6 7 55
how to Classify of email (pdf attachments) 7 32
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now