Progress bar for a subreport

[Robert Neville]

I would like to show a custom progress meter while opening a report. I
have spent the last four hours reviewing threads about the subject;
and most threads did not apply to my scenario. My report has numerous
sub-reports and the code uses a SQLwith .execute statement, which
require a significant amount of processing time.

Most threads suggest opening a form and loop through a recordset;
thereby obtaining the min and max values for the custom progress bar.
Most people get by with Access's SysCmd. I have a vague idea on how to
proceed without looping the progress code somehow. The sub-report
contributes to the slow down. But the real culprit lies in the "Make
Table" SQL that takes 5 minutes to execute on a relative speedy
machine. In fact, the "Make Table" approach alleviates the
shortcomings in using a query with complex functions as the record
source; which took hours to execute as sub-report record source. The
code executed the "Make Table" SQL and set the sub-report recordset to
this table. The sub-report takes another 60 to 90 seconds.

Let me know if you have any suggestions and dealing with this
scenario. The code below shows the complexity of this scenario.

Private Sub Report_Open(Cancel As Integer)

Static fInitialized As Boolean
Dim dbs As DAO.Database
Dim strSQL As String

Const cQ As String = """"
Set dbs = CurrentDb

If Not fInitialized Then

fInitialized = True
strSQL = "SELECT First(qryContProj.ProjID) AS ProjID, " _
& "First(qryContProj.JobTitle) AS JobTitle, " _
& "[qryContProj].[JobTitle] & " & cQ & ":» " & cQ & "
AS JobTitleLabel, " _
& "StringList(" & cQ & "[ContactName]" & cQ & "," & cQ
& "[qryContProj]" & cQ & "," & cQ & "[ProjID] =" & cQ & " & [ProjID] &
" & cQ & " AND [JobTitle]='" & cQ & " & [JobTitle] & " & cQ & "'" & cQ
& "," & cQ & " - " & cQ & ") AS ContactNames " _
& "INTO qryReport_rsubProjCont " _
& "FROM qryContProj " _
& "GROUP BY [qryContProj].[JobTitle] & " & cQ & ":» "
& cQ & ", " _
& "StringList(" & cQ & "[ContactName]" & cQ & "," & cQ
& "[qryContProj]" & cQ & "," & cQ & "[ProjID] =" & cQ & " & [ProjID] &
" & cQ & " AND [JobTitle]='" & cQ & " & [JobTitle] & " & cQ & "'" & cQ
& "," & cQ & " - " & cQ & ") " _
& "ORDER BY First(qryContProj.ProjID); "
Debug.Print "strSQL:» "; strSQL
dbs.Execute strSQL, dbFailOnError
Me.RecordSource = "qryReport_rsubProjCont"
End If

' Research on ideas for a custom progress bar
' varStatus = SysCmd(acSysCmdSetStatus, strString)
' varStatus = SysCmd(acSysCmdInitMeter, strString, intStatus)
' varStatus = SysCmd(acSysCmdUpdateMeter, intStatus)
' varStatus = SysCmd(acSysCmdRemoveMeter)
' varStatus = SysCmd(acSysCmdClearStatus)

' varStatus = SysCmd(acSysCmdGetObjectState)

' 1) You open your form (ProgressBar) -> DoCmd.OpenForm "ProgressBar";
' 2) You Initialize your ProgressBar -> Call InitProgressBar(MIN,MAX);
' 3) You Update your ProgressBar --> Call UpdateProgressBar(Value);
' 4) You close your form (ProgressBar) --> DoCmd.Close "ProgressBar"

' UpdateProgressBar 0, 1, currentProcess&, overallMax&, "Extracting
AS/400 reason codes for charge-backs"

Open_Report_Exit:
dbs.Close
Set dbs = Nothing

Open_Report_Err:
Call ErrMsgStd(Me.Name & "." & cstrProc, Err.Number,
Err.Description, True)
Resume Open_Report_Exit
End Sub

 

[SA]

Bob:

Once you hand off the execution to Jet via the Execute command, there's no
way to measure its progress. Too, when running VBA it generally run
synchronously so when you fire an execute the VBA won't continue until the
execute finishes (the only way around that would be to have it run in a
separate process.)

If your make table is that slow, then you must be querying against /
appending a huge number of records. There are a number of things that you
might try to speed things up:

1.) Create a regular select query to pull the records first, then use that
as the base for your grouping like FIRST etc. If you are running accross a
network, then do this to pull the select data locally into another temp
table, and then run the grouping make table query. Much less network
traffic will result increasing speed.
2.) If you are appending lots of records, you might try to break down the
number of records appended into small blocks, that will conserve memory and
work more efficiently.
3.) If you are doing any of this with SQL strings, create save queries that
do the same thing. This allows Jet to optimize its use of the indexes and
execution path.
4.) Check your indexes. Are all your WHERE parameters covered by an Index?
If you are grouping by first etc, you might look at indexing the fields
there if there is lots of variety in the data in field.
5.) If you are on an Access 2000 / 2002 db, also follow the items outlined
in Tony Toews suggestions at:
http://www.granite.ab.ca/access/performancefaq.htm

--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

I would like to show a custom progress meter while opening a report. I
have spent the last four hours reviewing threads about the subject;
and most threads did not apply to my scenario. My report has numerous
sub-reports and the code uses a SQLwith .execute statement, which
require a significant amount of processing time.

Most threads suggest opening a form and loop through a recordset;
thereby obtaining the min and max values for the custom progress bar.
Most people get by with Access's SysCmd. I have a vague idea on how to
proceed without looping the progress code somehow. The sub-report
contributes to the slow down. But the real culprit lies in the "Make
Table" SQL that takes 5 minutes to execute on a relative speedy
machine. In fact, the "Make Table" approach alleviates the
shortcomings in using a query with complex functions as the record
source; which took hours to execute as sub-report record source. The
code executed the "Make Table" SQL and set the sub-report recordset to
this table. The sub-report takes another 60 to 90 seconds.

Let me know if you have any suggestions and dealing with this
scenario. The code below shows the complexity of this scenario.

Private Sub Report_Open(Cancel As Integer)

Static fInitialized As Boolean
Dim dbs As DAO.Database
Dim strSQL As String

Const cQ As String = """"
Set dbs = CurrentDb

If Not fInitialized Then

fInitialized = True
strSQL = "SELECT First(qryContProj.ProjID) AS ProjID, " _
& "First(qryContProj.JobTitle) AS JobTitle, " _
& "[qryContProj].[JobTitle] & " & cQ & ":» " & cQ & "
AS JobTitleLabel, " _
& "StringList(" & cQ & "[ContactName]" & cQ & "," & cQ
& "[qryContProj]" & cQ & "," & cQ & "[ProjID] =" & cQ & " & [ProjID] &
" & cQ & " AND [JobTitle]='" & cQ & " & [JobTitle] & " & cQ & "'" & cQ
& "," & cQ & " - " & cQ & ") AS ContactNames " _
& "INTO qryReport_rsubProjCont " _
& "FROM qryContProj " _
& "GROUP BY [qryContProj].[JobTitle] & " & cQ & ":» "
& cQ & ", " _
& "StringList(" & cQ & "[ContactName]" & cQ & "," & cQ
& "[qryContProj]" & cQ & "," & cQ & "[ProjID] =" & cQ & " & [ProjID] &
" & cQ & " AND [JobTitle]='" & cQ & " & [JobTitle] & " & cQ & "'" & cQ
& "," & cQ & " - " & cQ & ") " _
& "ORDER BY First(qryContProj.ProjID); "
Debug.Print "strSQL:» "; strSQL
dbs.Execute strSQL, dbFailOnError
Me.RecordSource = "qryReport_rsubProjCont"
End If

' Research on ideas for a custom progress bar
' varStatus = SysCmd(acSysCmdSetStatus, strString)
' varStatus = SysCmd(acSysCmdInitMeter, strString, intStatus)
' varStatus = SysCmd(acSysCmdUpdateMeter, intStatus)
' varStatus = SysCmd(acSysCmdRemoveMeter)
' varStatus = SysCmd(acSysCmdClearStatus)

' varStatus = SysCmd(acSysCmdGetObjectState)

' 1) You open your form (ProgressBar) -> DoCmd.OpenForm "ProgressBar";
' 2) You Initialize your ProgressBar -> Call InitProgressBar(MIN,MAX);
' 3) You Update your ProgressBar --> Call UpdateProgressBar(Value);
' 4) You close your form (ProgressBar) --> DoCmd.Close "ProgressBar"

' UpdateProgressBar 0, 1, currentProcess&, overallMax&, "Extracting
AS/400 reason codes for charge-backs"

Open_Report_Exit:
dbs.Close
Set dbs = Nothing

Open_Report_Err:
Call ErrMsgStd(Me.Name & "." & cstrProc, Err.Number,
Err.Description, True)
Resume Open_Report_Exit
End Sub