Progress Bar During Recordset Query

I have a simple application that queries an Oracle datasource using ODBC and ADODB. The table that I'm hitting is immense and can at times take 10-15 minutes to respond to simple queries. I would like to have a simple graphical progress bar in my code to show the status of the query as it proceeds, however I thought that with ADODB using ODBC for a connection, it is impossible to have the database communicate back to the application the progress of the query until the recordset is returned? If thats the case then is it possible to just have a sort of "while you wait" progess bar (sort of like the progress bar you see when Windows XP boots up) that doesn't really correlate to the query of the recordset, just really lets the user know that the application is still searching for the records? Hers is my code for the connection:

HTML Code:

---

Option Explicit
Public rsmain As ADODB.Recordset
Public cn As ADODB.Connection
'connection variables
Public gstrDSN As String
Public gstrUser As String
Public gstrPassword As String
Public Function OpenDatabaseConnection() As Boolean
On Error GoTo errhandler
Dim connectionstring As String
    Set cn = New ADODB.Connection
    cn.Provider = "MSDAORA.1"
    cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN
    cn.CursorLocation = adUseClient
    cn.Open
    OpenDatabaseConnection = True
    Exit Function
errhandler:
    MsgBox "Connection failed, Enter Login Parameters Again", vbCritical
    OpenDatabaseConnection = False
    Exit Function
End Function

---

...and here is my code for the actual query:

HTML Code:

---

Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
Dim wasNull As Boolean
    If txtSKU.Text = "" Then
        MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
        txtSKU.SetFocus
        Exit Sub
    End If
    strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
            " from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
            " and store_cd = '" & cboFromStoreNo.Text & "'" & _
            " and other_store_cd = '" & cboToStoreNo.Text & "'"
    Set rsmain = New ADODB.Recordset
    rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
    If rsmain.EOF Then
        MsgBox "SKU data data does not exist for this location combination...", vbCritical
        txtSKU.Text = ""
        txtSKU.SetFocus
        Exit Sub
    Else
            blnflag = False
            For inti = 1 To MSFItem.Rows - 1
                If Left(MSFItem.TextMatrix(inti, 0), 9) = txtSKU.Text Then
                    blnflag = True
                    Exit For
                End If
            Next
            If blnflag = False Then
                Do While Not rsmain.EOF()
                    If MSFItem.TextMatrix(1, 0) = "" Then
                        inti = 1
                    Else
                        inti = MSFItem.Rows
                        MSFItem.Rows = MSFItem.Rows + 1
                    End If
                    wasNull = False
                    MSFItem.TextMatrix(inti, 0) = rsmain("trn_dt")
                    MSFItem.TextMatrix(inti, 1) = rsmain("ins_dt_time")
                    MSFItem.TextMatrix(inti, 2) = rsmain("sku_num")
                    MSFItem.TextMatrix(inti, 3) = rsmain("store_cd")
                    MSFItem.TextMatrix(inti, 4) = rsmain("other_store_cd")
                    MSFItem.TextMatrix(inti, 5) = rsmain("trn_tp")
                    MSFItem.TextMatrix(inti, 6) = rsmain("qty")
                    MSFItem.TextMatrix(inti, 7) = rsmain("ext_cst")
                    MSFItem.TextMatrix(inti, 8) = rsmain("ship_num")
                    MSFItem.TextMatrix(inti, 9) = rsmain("keyrec_num")
                    MSFItem.TextMatrix(inti, 10) = rsmain("processed_flag")
                    rsmain.MoveNext
                Loop
            Else
                MsgBox " This Item is already entered", vbInformation
            End If
            txtSKU.Text = ""
            txtSKU.SetFocus
            Exit Sub
        End If
    Exit Sub
errhandler:
    MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
    Exit Sub
End Sub

---

...how should I go about adding some type of graphical reference to let the user know that the application is still running?

Most programs that need to provide a visual component for the user to stare at while something loads and to make sure that they don't think the program crashed but don't know how long it will be displayed for will either show an animated picture of some sort, or a looping progress bar (such as the one on the Windows XP splash screen).

Here are the basics of a progress bar

You could set the Max to the number of rows, and advance the value for each row. This example works quickly, but take notice of the DoEvents staement. Without it, the form would never show completely until the loop was finished.

VB Code:

Option Explicit
 
Private Sub Form_Activate()
  ProgressBar1.Min = 0
  ProgressBar1.Max = 100
  ProgressBar1.Value = 0
  ProgressBar1.Enabled = True
  Dim x As Integer
  Dim q As Long
  For x = 0 To 100
    doevents
    For q = 1 To 500000
    Next q
    ProgressBar1.Value = x
  Next x
  Unload Me
End Sub

An ADO recordset actually have events. Try declararing the recordset using the WithEvents keyword.

VB Code:

Private WithEvents rsmain As Recordset
 
Private Sub rsmain_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, _
 adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    'do whatever
End Sub

OK now I'm completely confused.... Lets say that all I want to do is have a new form (frmProgress) that pops up when the user chooses "Enter" and has an animation (AVI) file play while the query is taking place. Then when the application returns a recordset "frmProgress" will dissappear. On "frmProgress" I'll have a message that says something like, "Searching database, please wait...." and then have the animation in the loop. Does anybody have a good example of this, I'm stumped? To make the new form appear I'd do something like:

HTML Code:

---

Private Sub cmdEnter_Click()
frmProgress.Show
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
'Dim wasNull As Boolean
    If txtSKU.Text = "" Then
        MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
        txtSKU.SetFocus
        Exit Sub
    End If
    strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
            " from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
            " and store_cd = '" & cboFromStoreNo.Text & "'" & _
            " and other_store_cd = '" & cboToStoreNo.Text & "'"
    Set rsmain = New ADODB.Recordset
    rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
    frmProgress.Hide
    If rsmain.EOF Then
        MsgBox "SKU data data does not exist for this location combination...", vbCritical
        txtSKU.Text = ""
        txtSKU.SetFocus
        Exit Sub
    Else
            blnflag = False
            For inti = 1 To MSFItem.Rows - 1
                If Left(MSFItem.TextMatrix(inti, 0), 9) = txtSKU.Text Then
                    blnflag = True
                    Exit For
                End If
            Next
            If blnflag = False Then
                Do While Not rsmain.EOF()
                    If MSFItem.TextMatrix(1, 0) = "" Then
                        inti = 1
                    Else
                        inti = MSFItem.Rows
                        MSFItem.Rows = MSFItem.Rows + 1
                    End If
                    wasNull = False
                    MSFItem.TextMatrix(inti, 0) = rsmain("trn_dt")
                    MSFItem.TextMatrix(inti, 1) = rsmain("ins_dt_time")
                    MSFItem.TextMatrix(inti, 2) = rsmain("sku_num")
                    MSFItem.TextMatrix(inti, 3) = rsmain("store_cd")
                    MSFItem.TextMatrix(inti, 4) = rsmain("other_store_cd")
                    MSFItem.TextMatrix(inti, 5) = rsmain("trn_tp")
                    MSFItem.TextMatrix(inti, 6) = rsmain("qty")
                    MSFItem.TextMatrix(inti, 7) = rsmain("ext_cst")
                    MSFItem.TextMatrix(inti, 8) = rsmain("ship_num")
                    MSFItem.TextMatrix(inti, 9) = rsmain("keyrec_num")
                    MSFItem.TextMatrix(inti, 10) = rsmain("processed_flag")
                    rsmain.MoveNext
                Loop
            Else
                MsgBox " This Item is already entered", vbInformation
            End If
            txtSKU.Text = ""
            txtSKU.SetFocus
            Exit Sub
        End If
    Exit Sub
errhandler:
    MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
    Exit Sub
End Sub

---

Got it working with Animation Control, however when I call the form containing the AVI it just blanks out while the query is taking place?

Quote:

---

Originally Posted by Heprox

Got it working with Animation Control, however when I call the form containing the AVI it just blanks out while the query is taking place?

---

That's because VB is busy running the query and it wont update the AVI control until it's done. In other words it's pretty useless. Why can't you use the events as I suggested earlier? You could atleast put a DoEvents in that event so the Animation control can refresh itself. Or you could update a progressbar with the information passed as arguments to the event.

I would, unfortunately I dont understand exactly how that would work with my code? Are you saying that ADODB is returning information about the progress of the queried recordsets, then update the progress bar accordingly? I was pretty sure ADODB didn't return anything to the application other than the final records retreived?

What I mean is that a ADODB Recordset raises events just as any control you could add on a Form. You only need to declare your recordset using the WithEvents keyword.

VB Code:

Private WithEvents rsmain As Recordset

If you've done that you can select rsmain in the Object dropdown list of the code editor and in the events dropdown select the FetchProgress event VB will then create the following event sub in your code:

VB Code:

Private Sub rsmain_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, _
 adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
 
End Sub

As you can see this event has a Progress and a MaxProgress argument that will be filled with the current Progress of the maximum MaxProgress. So the percentage of the progress will be (MaxProgress / Progress) * 100 (%).

Correct, I understood that part, where I'm confused I guess, is where does that fit into my code? I understand that portion, but I don't get how that relates to the actual query retrieving the recordsets? You see when I send the query "strsql" here:

HTML Code:

---

Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
    If txtSKU.Text = "" Then
        MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
        txtSKU.SetFocus
        Exit Sub
    End If
    strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
            " from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
            " and store_cd = '" & cboFromStoreNo.Text & "'" & _
            " and other_store_cd = '" & cboToStoreNo.Text & "'"
    Set rsmain = New ADODB.Recordset
    rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly

---

...does ADODB return the progress of that query, I thought that it did not? I was under the impression that ADODB just returned the actual recordset and let the application finish? My query takes 10-15 minutes to complete, but actually only usually returns 3 or 4 records (its hitting a table with well over 550 million records). I'm a little confused on how the actual progress bar interacts with this?

You create a recordset using the New keyword, right? You then run a query using that recordset object. During the time the query is running the recordset object will raise the FetchProgress event.

So you can just add a progress bar control to a form and reference the FetchProgress event how? Sorry to be a pain, but I'm jst having a hard time understanding the control.

Look at the attached image...

Correct, I've already done all of that, now how does the actual progress bar control interact with it? Thanks for your help.

Something like this:

VB Code:

Private Sub rsmain_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, _
 adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    With frmProgress.ProgressBar1
        .Max = MaxProgress
        .Value = Progress
        .Refresh
    End With
End Sub

OK, that makes more sense. I dropped a progress bar control onto my form and now my codes looks like:

HTML Code:

---

Private Sub rsmain_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, _
 adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    With frmProgress.ProgressBar1
        .Max = MaxProgress
        .value = Progress
        .Refresh
    End With
End Sub

---

...my question is, now how do you attach the status of the query via ADO to the progress bar, to see the bar update as the query progresses? Here is the final code for the query (I had to make some corrections for NULL objects):

HTML Code:

---

Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
Dim wasNull As Boolean
    If txtSKU.Text = "" Then
        MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
        txtSKU.SetFocus
        Exit Sub
    End If
    strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
            " from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
            " and store_cd = '" & cboFromStoreNo.Text & "'" & _
            " and other_store_cd = '" & cboToStoreNo.Text & "'"
    Set rsmain = New ADODB.Recordset
    rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
    If rsmain.EOF Then
        MsgBox "SKU data data does not exist for this location combination...", vbCritical
        txtSKU.Text = ""
        txtSKU.SetFocus
        Exit Sub
    Else
            blnflag = False
            For inti = 1 To MSFItem.Rows - 1
                If Left(MSFItem.TextMatrix(inti, 0), 11) = txtSKU.Text Then
                    blnflag = True
                    Exit For
                End If
            Next
            If blnflag = False Then
                Do While Not rsmain.EOF()
                    If MSFItem.TextMatrix(1, 0) = "" Then
                        inti = 1
                    Else
                        inti = MSFItem.Rows
                        MSFItem.Rows = MSFItem.Rows + 1
                    End If
                    wasNull = False
                    MSFItem.TextMatrix(inti, 0) = IfNull(rsmain("trn_dt"), wasNull, "")
                    MSFItem.TextMatrix(inti, 1) = IfNull(rsmain("ins_dt_time"), wasNull, "")
                    MSFItem.TextMatrix(inti, 2) = rsmain("sku_num")
                    MSFItem.TextMatrix(inti, 3) = rsmain("store_cd")
                    MSFItem.TextMatrix(inti, 4) = rsmain("other_store_cd")
                    MSFItem.TextMatrix(inti, 5) = IfNull(rsmain("trn_tp"), wasNull, "")
                    MSFItem.TextMatrix(inti, 6) = IfNull(rsmain("qty"), wasNull, "")
                    MSFItem.TextMatrix(inti, 7) = IfNull(rsmain("ext_cst"), wasNull, "")
                    MSFItem.TextMatrix(inti, 8) = IfNull(rsmain("ship_num"), wasNull, "")
                    MSFItem.TextMatrix(inti, 9) = IfNull(rsmain("keyrec_num"), wasNull, "")
                    MSFItem.TextMatrix(inti, 10) = IfNull(rsmain("processed_flag"), wasNull, "")
                    rsmain.MoveNext
                Loop
            Else
                MsgBox " This SKU is already entered", vbInformation
            End If
            txtSKU.Text = ""
            txtSKU.SetFocus
            Exit Sub
        End If
    Exit Sub
errhandler:
    MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
    Exit Sub
End Sub

---

...again, thanks for all your help.

Quote:

---

Originally Posted by Heprox

OK, that makes more sense. I dropped a progress bar control onto my form and now my codes looks like:

HTML Code:

---

Private Sub rsmain_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, _
 adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    With frmProgress.ProgressBar1
        .Max = MaxProgress
        .value = Progress
        .Refresh
    End With
End Sub

---

...my question is, now how do you attach the status of the query via ADO to the progress bar, to see the bar update as the query progresses? Here is the final code for the query (I had to make some corrections for NULL objects):

HTML Code:

---

Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
Dim wasNull As Boolean
    If txtSKU.Text = "" Then
        MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
        txtSKU.SetFocus
        Exit Sub
    End If
    strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
            " from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
            " and store_cd = '" & cboFromStoreNo.Text & "'" & _
            " and other_store_cd = '" & cboToStoreNo.Text & "'"
    Set rsmain = New ADODB.Recordset
    rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
    If rsmain.EOF Then
        MsgBox "SKU data data does not exist for this location combination...", vbCritical
        txtSKU.Text = ""
        txtSKU.SetFocus
        Exit Sub
    Else
            blnflag = False
            For inti = 1 To MSFItem.Rows - 1
                If Left(MSFItem.TextMatrix(inti, 0), 11) = txtSKU.Text Then
                    blnflag = True
                    Exit For
                End If
            Next
            If blnflag = False Then
                Do While Not rsmain.EOF()
                    If MSFItem.TextMatrix(1, 0) = "" Then
                        inti = 1
                    Else
                        inti = MSFItem.Rows
                        MSFItem.Rows = MSFItem.Rows + 1
                    End If
                    wasNull = False
                    MSFItem.TextMatrix(inti, 0) = IfNull(rsmain("trn_dt"), wasNull, "")
                    MSFItem.TextMatrix(inti, 1) = IfNull(rsmain("ins_dt_time"), wasNull, "")
                    MSFItem.TextMatrix(inti, 2) = rsmain("sku_num")
                    MSFItem.TextMatrix(inti, 3) = rsmain("store_cd")
                    MSFItem.TextMatrix(inti, 4) = rsmain("other_store_cd")
                    MSFItem.TextMatrix(inti, 5) = IfNull(rsmain("trn_tp"), wasNull, "")
                    MSFItem.TextMatrix(inti, 6) = IfNull(rsmain("qty"), wasNull, "")
                    MSFItem.TextMatrix(inti, 7) = IfNull(rsmain("ext_cst"), wasNull, "")
                    MSFItem.TextMatrix(inti, 8) = IfNull(rsmain("ship_num"), wasNull, "")
                    MSFItem.TextMatrix(inti, 9) = IfNull(rsmain("keyrec_num"), wasNull, "")
                    MSFItem.TextMatrix(inti, 10) = IfNull(rsmain("processed_flag"), wasNull, "")
                    rsmain.MoveNext
                Loop
            Else
                MsgBox " This SKU is already entered", vbInformation
            End If
            txtSKU.Text = ""
            txtSKU.SetFocus
            Exit Sub
        End If
    Exit Sub
errhandler:
    MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
    Exit Sub
End Sub

---

...again, thanks for all your help.

---

I also do have same issue, FetchProgress does not trigger at all.. Have you resolved your issue or any success?
help in this regard is most appreciated.

Quote:

---

Originally Posted by confeder8

I also do have same issue, FetchProgress does not trigger at all.. Have you resolved your issue or any success?
help in this regard is most appreciated.

---

Most of the progress events on ADODB.Recordset are raised for client-side cursors only, although using these to display progress is a lost cause as most of the time is usually spent by RDBMS executing the query (think of JOINs and filtering in WHERE clause and grouping with GROUP BY clause and sorting with ORDER BY) which takes most of the time.

The actual network transport, which is what FetchProgess indicates, usually takes significantly less time compared to the query plan execution beforehand.

cheers,
</wqw>

Also, next time, instead of resurrecting an old thread where it's likely most if not all people invilved in it are long gone and not around anymore (I think resurrecting a 17-yo thread takes top prize for oldest revival) ... instead, create a new thread, link to the old one, and then give a description of hte problem.

-tg