Solved

Progress Bar with SQL query

Posted on 2001-07-03
10
729 Views
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.

elzohry
0
Comment
Question by:elzohry
  • 2
  • 2
  • 2
  • +4
10 Comments
 
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.
0
 
LVL 38

Expert Comment

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

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

Expert Comment

by:AzraSound
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...
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 3

Expert Comment

by:nigelrowe
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.
0
 
LVL 4

Expert Comment

by:Nazdor
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


0
 
LVL 43

Accepted Solution

by:
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"
    cnnLive.Open
    With rstLogInfo
        .CursorLocation = adUseClient
        pgbFetch.Max = lngMax
        .Open "Select * From LotBaseLog", cnnLive, adOpenStatic, adLockReadOnly, adAsyncFetch
    End With
End Sub
Private Sub Command2_Click()
    rstLogInfo.Close
    cnnLive.Close
    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
    DoEvents
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.
0
 
LVL 4

Expert Comment

by:Nazdor
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
0
 
LVL 3

Expert Comment

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

Expert Comment

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

Author Comment

by:elzohry
ID: 6255180
Thanks for All, special thanks to AzraSound
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

856 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