Progress Meter
JK
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
Premature send event
Appreciate you help and happy New year.
Regards
Jacob
"JK" <Nob...@Home.com> wrote in message
news:uaj%23QbILH...@TK2MSFTNGP04.phx.gbl...
Albert D. Kallal
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.....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
Koos
'...
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)
"JK" <Nob...@Home.com> schreef in bericht
news:uaj%23QbILH...@TK2MSFTNGP04.phx.gbl...
![Arvin Meyer [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjWNeBaELXBA4T-Yh0e0NpfK3HiG4ga607fRgadqcjevhVaQ=s40-c)
Arvin Meyer [MVP]
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
"JK" <Nob...@Home.com> wrote in message
news:uaj%23QbILH...@TK2MSFTNGP04.phx.gbl...
JK
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
"Arvin Meyer [MVP]" <a...@m.com> wrote in message
news:uoLuWYQL...@TK2MSFTNGP06.phx.gbl...
JK
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
"Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in message
news:eyL7sAOL...@TK2MSFTNGP02.phx.gbl...
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
Perfect, that what the doctor orderd :-)
Regards
Jacob
"Koos" <kdet...@wanadoo.nl> wrote in message
news:4597d232$0$80495$dbd4...@news.wanadoo.nl...
JK
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
"JK" <Nob...@Home.com> wrote in message
news:%23tLOJFR...@TK2MSFTNGP06.phx.gbl...
Ken Snell (MVP)
news:uoLuWYQL...@TK2MSFTNGP06.phx.gbl...
> 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.
--
Ken Snell
<MS ACCESS MVP>
Arvin Meyer [MVP]
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
"JK" <Nob...@Home.com> wrote in message
news:uTttOvQL...@TK2MSFTNGP06.phx.gbl...
JK
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
"Arvin Meyer [MVP]" <a...@m.com> wrote in message
news:%23outkpR...@TK2MSFTNGP06.phx.gbl...
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
You will find buried in here
"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:b5fgp25om133bdrg3...@4ax.com...
Arvin Meyer [MVP]
"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:b5fgp25om133bdrg3...@4ax.com...
> 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]
DoEvents in the code after opening the form to give it time to paint.
"JK" <Nob...@Home.com> wrote in message
news:uulucWS...@TK2MSFTNGP06.phx.gbl...
John Vinson
wrote:
>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
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]" <a...@m.com> wrote in message
news:eVLQZ3aL...@TK2MSFTNGP02.phx.gbl...
Arvin Meyer [MVP]
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.
"JK" <Nob...@Home.com> wrote in message
news:eIFzNWhL...@TK2MSFTNGP04.phx.gbl...