Progress Meter

[JK]

Hi to all

I am trying to use the progress meter when looping in a recordset of some
300 records and growing. As it is now, it takes about 20 seconds which is
far too long for a user to wait, not knowing how long he/she have to wait.

For the life of me, I cannot decipher the Help pages on the issue. As this
is a "generic" question, let us assume the following:

Dim s as string, rs as DAO.Recordset

s="SELECT [Field1],[Field2], etc FROM [MyTable];"
Set rs=CurrentDb.OpenRecordset(s)
rs.MoveFirst
Do While Not rs.EOF

Do something and display the progress meter with some massage
eg "calculating, please wait " follows by the progress meter
or better still the massge the the user which record is calculated now

Loop

 

[JK]

Opps,
Premature send event

Appreciate you help and happy New year.

Regards
Jacob

 

[Albert D. Kallal]

I am not sure why processing 300 records would take more then 1 second...let
alone 20....

Unless you doing protein folding, or generating scenes for the sequence to
Gears of War on the xbox 360, a small reocrdset of 300 records should not
take very much time.


The follwing should give you an idea of what levels
of perforamnce can be obtained in ms-access:

Lets assume a typical products database and we want to keep
inventory..

Lets assume 500 products in the product table. = 500 records

Lets assume that we had 5 stock taking days where we added stock to EACH
product 5 TIMES this year.
(so, each stock item was re-stocked 5 times during the year. Note that the
sample given design allows for price changes as new stock arrives).

That now means that our Inventory table has 2500 records.

Lets also assume that each Inventory item has 50 orders in the invoices
(order details) table on average.

That now means our Orders Details table has 50 * 2500 = 125,000 records.

So, what we want to do is calculate quantity on hand.

So, we got 125,000 detail records, and 2500 inventory items (and 500
products.

Remember, in a modern system, we do NOT store the quality on hand, but must
calculate it on the fly.

The beauty of this approach is that I can then simply delete, or add, or
modify records, and the totals for inventory is always 100% correct.

Further, I can just paint away with the
forms designer and build sub forms etc for invoice details where users
can enter the quantity ordered. Again, no special code is needed to
update the inventory stocks since I *CALCULATE* it on the fly
when needed.

That means the sql has to join up all records that belong to each
product..and sum them, and then subtract the quantities in the
invoice details.

Time to total up all of the in-stock records (that is running sql
statements to total all Inventory additions less all those 125,000 order
details to come up with a total for EACH product.???

On a average pc today, ms-access will total up and generate the quality on
hand for those 125,000 detail of is LESS then 1 second. (this time
*INCLUDES* the matching, adding, and subtracting of all orders in the
system).

That is right....125,000 records get matched up, and inventory totals TAKE
LESS THEN ONE second to generate.

I gave you the long explain becuase I wanted to give you some idea of the
INCREDALBE perforance that the JET database enigne that ms-access uses. it
is fast....VERY fast, and 300 reocrds likey should not generate any
noticalbe delay...

As for a nice progress bar? I have a working example here:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Download the "super easy word" merge. There is buttion on the form that
shows how to make a progess bar.....

 

[Koos]

Dim VarRet as Variant, intLoop1 as Integer
'...
rst.MoveLast
' Turn on the progress meter on the status bar
varRet = SysCmd(acSysCmdInitMeter, "calculating, please wait",
rst.RecordCount)
rst.MoveFirst
Do While Not rs.EOF

Do something and display the progress meter with some massage
eg "calculating, please wait " follows by the progress meter
or better still the massge the the user which record is calculated now

intLoop1 = intLoop1 + 1
varRet = SysCmd(acSysCmdUpdateMeter, intLoop1)
Loop
varRet = SysCmd(acSysCmdClearStatus)

 

[Arvin Meyer [MVP]]

You can use the status bar one, but I've found no one looks there. The
leaves you with several other choices. I usuallu just put up a "Please Wait"
form before the query starts and close it when the query finished. If you
want to get more sophisticated, you can alter the free Progress Bar chart
tool on my website:

http://www.datastrat.com/Download/ProgressBar2K.zip

 

[JK]

Thanks Arvin,

I had thought about and tend to agree with your approach, I might actually
settle on that. However, for my education I will still want to try the
progress meter albeit I might not use it on this occasion.

FYI, if you recall out previous discussion on the subject, using recordset
rather then update query reduced the time of updating the table from about
10/15 minutes to 20 second

Regards
Jacob

You can use the status bar one, but I've found no one looks there. The
leaves you with several other choices. I usuallu just put up a "Please
Wait" form before the query starts and close it when the query finished.
If you want to get more sophisticated, you can alter the free Progress Bar
chart tool on my website:

http://www.datastrat.com/Download/ProgressBar2K.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi to all

I am trying to use the progress meter when looping in a recordset of some
300 records and growing. As it is now, it takes about 20 seconds which is
far too long for a user to wait, not knowing how long he/she have to
wait.

For the life of me, I cannot decipher the Help pages on the issue. As
this is a "generic" question, let us assume the following:

Dim s as string, rs as DAO.Recordset

s="SELECT [Field1],[Field2], etc FROM [MyTable];"
Set rs=CurrentDb.OpenRecordset(s)
rs.MoveFirst
Do While Not rs.EOF

Do something and display the progress meter with some massage
eg "calculating, please wait " follows by the progress meter
or better still the massge the the user which record is calculated now

Loop

 

[JK]

Thanks Albert,

It a bit more complex that straight forward update of a table such as
inventories or price changes in product table.

This is a table that hold data about holidays around the worlds. Each record
holds the rule for a holiday, from the most simple such as 1 Jan or 25 Dec
each year (happy New year) or 2nd Sunday In April by its relevant *calendar*
(Gregorian, Hebrew, Islamic thus far).

Further some holidays depending on other holidays e.g. 46 days after Easter
Sunday, meaning the "linked" holiday has to be calculated first".

Some holidays also have exceptions. eg, If a holiday falls on a weekend move
it to Monday, or add an holiday on Monday, or, a holiday do not occur yearly
(US Presidential Election date)

Once a year, usually on 1 January, I ran and update on the whole table
updating 2 fields "This Year Date" (which may be null) and "Next Date" which
always has a value. If "This Year Date" already passed, "Next Date" will get
the value of next year date, otherwise they are both the same, assuming
yearly holiday.

I guess that you can see why it takes some 20 seconds to update some 300
records (using update query takes 10/15 minutes)

Each holiday is automatically updated when the user enter a record through
the Current event, no problem there, but as I need to keep a warning on
"Coming Holidays" the I cannot rely on the user to go to each individual
record.

Many thanks for your detailed reply and the sample (I will have a look at
it)

Regards
Jacob

 

[Tony Toews]

You can use the status bar one, but I've found no one looks there.

Yup, in Access 2.0 it was nice and large and folks really noticed it.
But that got "downsized" in A95 I think. Definitely in A97.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[JK]

Thanks Koos,

Perfect, that what the doctor orderd

Regards
Jacob

Dim VarRet as Variant, intLoop1 as Integer
'...
rst.MoveLast
' Turn on the progress meter on the status bar
varRet = SysCmd(acSysCmdInitMeter, "calculating, please wait",
rst.RecordCount)
rst.MoveFirst
Do While Not rs.EOF

Do something and display the progress meter with some massage
eg "calculating, please wait " follows by the progress meter
or better still the massge the the user which record is calculated now

intLoop1 = intLoop1 + 1
varRet = SysCmd(acSysCmdUpdateMeter, intLoop1)
Loop
varRet = SysCmd(acSysCmdClearStatus)

Hi to all

I am trying to use the progress meter when looping in a recordset of some
300 records and growing. As it is now, it takes about 20 seconds which is
far too long for a user to wait, not knowing how long he/she have to
wait.

For the life of me, I cannot decipher the Help pages on the issue. As
this
is a "generic" question, let us assume the following:

Dim s as string, rs as DAO.Recordset

s="SELECT [Field1],[Field2], etc FROM [MyTable];"
Set rs=CurrentDb.OpenRecordset(s)
rs.MoveFirst
Do While Not rs.EOF

Do something and display the progress meter with some massage
eg "calculating, please wait " follows by the progress meter
or better still the massge the the user which record is calculated
now

Loop

 

[JK]

By the way,

Being 1 January (here) today, the update takes about 11 seconds. The reason
being that [Next Date]=[This year date] for all the records. Naturally on
31 December, is the exact opposite, *all* [Next Date] have to be
recalculated.

Regards
Jacob

 

[Ken Snell \(MVP\)]

You can use the status bar one, but I've found no one looks there.

And, if you're running an .mde or a Runtime installation, the Status bar may
not even be visible.

 

[Arvin Meyer [MVP]]

It's been a while since I wrote that Progress bar. If I remember correctly,
you should be able to do a recordset.count and set the total records to it,
then update the other textbox with the current record count something like
(air code - untested):

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Long
Dim ctr As Long

Set db = CurrentDb
Set rst = db.OpenRecordset ("Your Query statement")

ctr = 0

rst.MoveLast
rst.MoveFirst
Me.txtTotal = rst.RecordCount

DoCmd.OpenForm frmProgressBar

For i = 0 to rst.RecordCount - 1
ctr = ctr + 1
Forms!frmProgressBar!txtCurrent = ctr

Call PctMeter(Forms!frmProgressBar!txtCurrent,
Forms!frmProgressBar!txtTotal)

' Do your stuff

rst.MoveNext
Next i
DoCmd.Close acForm "frmProgressBar"

Also, make sure that you move the PctMeter code in the ProgressBar form to a
standard module, and change all the references from Me to
Forms!frmProgressBar
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks Arvin,

I had thought about and tend to agree with your approach, I might
actually settle on that. However, for my education I will still want to
try the progress meter albeit I might not use it on this occasion.

FYI, if you recall out previous discussion on the subject, using recordset
rather then update query reduced the time of updating the table from about
10/15 minutes to 20 second

Regards
Jacob

You can use the status bar one, but I've found no one looks there. The
leaves you with several other choices. I usuallu just put up a "Please
Wait" form before the query starts and close it when the query finished.
If you want to get more sophisticated, you can alter the free Progress
Bar chart tool on my website:

http://www.datastrat.com/Download/ProgressBar2K.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi to all

I am trying to use the progress meter when looping in a recordset of
some 300 records and growing. As it is now, it takes about 20 seconds
which is far too long for a user to wait, not knowing how long he/she
have to wait.

For the life of me, I cannot decipher the Help pages on the issue. As
this is a "generic" question, let us assume the following:

Dim s as string, rs as DAO.Recordset

s="SELECT [Field1],[Field2], etc FROM [MyTable];"
Set rs=CurrentDb.OpenRecordset(s)
rs.MoveFirst
Do While Not rs.EOF

Do something and display the progress meter with some massage
eg "calculating, please wait " follows by the progress meter
or better still the massge the the user which record is calculated
now

Loop

 

[JK]

I have tried something along those lines.

The form partially opens and stays in limbo until the end of the update when
it shows the final result i.e "record 294 of 294" and progress meter fully
populated.

I'll try and see if it will work when I *first* open the form and do the
update as a private sub of the form (at the moment it is a public sub)

Regards
Jacob

It's been a while since I wrote that Progress bar. If I remember
correctly, you should be able to do a recordset.count and set the total
records to it, then update the other textbox with the current record count
something like (air code - untested):

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Long
Dim ctr As Long

Set db = CurrentDb
Set rst = db.OpenRecordset ("Your Query statement")

ctr = 0

rst.MoveLast
rst.MoveFirst
Me.txtTotal = rst.RecordCount

DoCmd.OpenForm frmProgressBar

For i = 0 to rst.RecordCount - 1
ctr = ctr + 1
Forms!frmProgressBar!txtCurrent = ctr

Call PctMeter(Forms!frmProgressBar!txtCurrent,
Forms!frmProgressBar!txtTotal)

' Do your stuff

rst.MoveNext
Next i
DoCmd.Close acForm "frmProgressBar"

Also, make sure that you move the PctMeter code in the ProgressBar form to
a standard module, and change all the references from Me to
Forms!frmProgressBar
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks Arvin,

I had thought about and tend to agree with your approach, I might
actually settle on that. However, for my education I will still want to
try the progress meter albeit I might not use it on this occasion.

FYI, if you recall out previous discussion on the subject, using
recordset rather then update query reduced the time of updating the table
from about 10/15 minutes to 20 second

Regards
Jacob

You can use the status bar one, but I've found no one looks there. The
leaves you with several other choices. I usuallu just put up a "Please
Wait" form before the query starts and close it when the query finished.
If you want to get more sophisticated, you can alter the free Progress
Bar chart tool on my website:

http://www.datastrat.com/Download/ProgressBar2K.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi to all

I am trying to use the progress meter when looping in a recordset of
some 300 records and growing. As it is now, it takes about 20 seconds
which is far too long for a user to wait, not knowing how long he/she
have to wait.

For the life of me, I cannot decipher the Help pages on the issue. As
this is a "generic" question, let us assume the following:

Dim s as string, rs as DAO.Recordset

s="SELECT [Field1],[Field2], etc FROM [MyTable];"
Set rs=CurrentDb.OpenRecordset(s)
rs.MoveFirst
Do While Not rs.EOF

Do something and display the progress meter with some massage
eg "calculating, please wait " follows by the progress meter
or better still the massge the the user which record is calculated
now

Loop

 

[John Vinson]

FYI, if you recall out previous discussion on the subject, using recordset
rather then update query reduced the time of updating the table from about
10/15 minutes to 20 second

VERY interesting. My usual experience has been exactly the opposite -
update queries usually much faster than recordsets.

Obviously it depends on just WHAT'S being updated and how; I'll dig
for your previous threads just from curiosity!

John W. Vinson[MVP]

 

[JK]

John,

You will find buried in here


http://groups-beta.google.com/group...browse_thread/thread/c56c9fc3ee48b6cc/?hl=en#

 

[Arvin Meyer [MVP]]

VERY interesting. My usual experience has been exactly the opposite -
update queries usually much faster than recordsets.

I have to agree with John. An update query is usually many times faster than
walking a recordset. Using SQL is almost always faster because it is
optimized and compiled before running. The few times it's slower are usually
because indexes are improperly designed or used. In that case it is often
speedier to use code to drop the indexes, run the update, then rebuild the
indexes. Appends suffer from this problem more than updates.

 

[Arvin Meyer [MVP]]

Probably because you don't have a fast enough graphics card. Try throwing a
DoEvents in the code after opening the form to give it time to paint.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have tried something along those lines.

The form partially opens and stays in limbo until the end of the update
when
it shows the final result i.e "record 294 of 294" and progress meter fully
populated.

I'll try and see if it will work when I *first* open the form and do the
update as a private sub of the form (at the moment it is a public sub)

Regards
Jacob

It's been a while since I wrote that Progress bar. If I remember
correctly, you should be able to do a recordset.count and set the total
records to it, then update the other textbox with the current record
count something like (air code - untested):

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Long
Dim ctr As Long

Set db = CurrentDb
Set rst = db.OpenRecordset ("Your Query statement")

ctr = 0

rst.MoveLast
rst.MoveFirst
Me.txtTotal = rst.RecordCount

DoCmd.OpenForm frmProgressBar

For i = 0 to rst.RecordCount - 1
ctr = ctr + 1
Forms!frmProgressBar!txtCurrent = ctr

Call PctMeter(Forms!frmProgressBar!txtCurrent,
Forms!frmProgressBar!txtTotal)

' Do your stuff

rst.MoveNext
Next i
DoCmd.Close acForm "frmProgressBar"

Also, make sure that you move the PctMeter code in the ProgressBar form
to a standard module, and change all the references from Me to
Forms!frmProgressBar
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks Arvin,

I had thought about and tend to agree with your approach, I might
actually settle on that. However, for my education I will still want to
try the progress meter albeit I might not use it on this occasion.

FYI, if you recall out previous discussion on the subject, using
recordset rather then update query reduced the time of updating the
table from about 10/15 minutes to 20 second

Regards
Jacob

You can use the status bar one, but I've found no one looks there. The
leaves you with several other choices. I usuallu just put up a "Please
Wait" form before the query starts and close it when the query
finished. If you want to get more sophisticated, you can alter the free
Progress Bar chart tool on my website:

http://www.datastrat.com/Download/ProgressBar2K.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi to all

I am trying to use the progress meter when looping in a recordset of
some 300 records and growing. As it is now, it takes about 20 seconds
which is far too long for a user to wait, not knowing how long he/she
have to wait.

For the life of me, I cannot decipher the Help pages on the issue. As
this is a "generic" question, let us assume the following:

Dim s as string, rs as DAO.Recordset

s="SELECT [Field1],[Field2], etc FROM [MyTable];"
Set rs=CurrentDb.OpenRecordset(s)
rs.MoveFirst
Do While Not rs.EOF

Do something and display the progress meter with some massage
eg "calculating, please wait " follows by the progress meter
or better still the massge the the user which record is calculated
now

Loop

 

[John Vinson]

The few times it's slower are usually
because indexes are improperly designed or used. In that case it is often
speedier to use code to drop the indexes, run the update, then rebuild the
indexes. Appends suffer from this problem more than updates.

I once worked with a big application (20 years ago, in Oracle) where
we had to do that. We needed heavy indexing for the intensive online
searching to be fast enough... but batch-loading data into the tables
could take 20 HOURS (on a fast VAX).

Dropping indexes, loading the data, rebuilding the indexes dropped
this to under an hour. Similar problems can come up in Access.

John W. Vinson[MVP]

 

[JK]

Arvin, John, Ken and Tony

Firstly, thank you all for taking the time to help
Having read all your comments I *have* to agree <g>, However:

Please bear in mind that my Update query has two similar functions (updating
two fields) each with quite a few DLookup() in more than one table which in
turn, if need be, calling other functions with or without some more
DLookup()

My suspicion was, which Arvin confirmed at the time, that the DLookup() was
the "offender" and as it turned out this the case.

For Arvin (who has my DB) please compare the function "HolidayDate" which
was used in the update query and the sub "RecalcThisYearDates", both in
module "Holidays Rules"

Happy new year
Jacob

 

[Arvin Meyer [MVP]]

I still haven't had time to look at your code, but I can tell you without
looking that DLookup is always slower than pulling a recordset. The fastest
way to work is to use a query to pull only the records you need. Do the
calculations on those records using a second query based upon the first.
Using 2 queries like that will reduce minutes to seconds.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com