Anyone know of a good progress bar code?

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a database with a large number of records. There are a couple of
queries that take a while and I was wondering whether I could create a
progress bar like that used for installing software so that the user knows
something is happening and when it finishes.
Thanks
Tony
 
I have a database with a large number of records. There are a couple of
queries that take a while and I was wondering whether I could create a
progress bar like that used for installing software so that the user
knows something is happening and when it finishes. Thanks
Tony
Click to expand...

I don't think so. A progress bar implies multiple processes performed in
sequence and it "progresses" as each individual process completes. To
your Access app the running of a query (regardless of how long it takes)
is a single process.

Your progress bar would sit at zero until the query was finished and then
it would jump to 100% all at once when it finished. The query does not
expose to your code anything to indicate how far along it is.

Ever notice how progress bars for installing software often do the same
thing? They tick along for a while and then pause for a protracted
period only to jump a whole bunch. Same principle. While it is
measuring multiple steps, some of the steps represent a small piece of
the total and some represent big chunks of the total. Your long running
query is one big chunk that represents 100% of the progress you are
trying to show the user.

As an aside, what specifically are you describing as "a large number of
records" and "queries that take a while"? I consider (almost) all
queries that takes longer than 10 seconds to be a problem of design. You
might post your structures and your query SQL and get some tips on how to
make them run faster.
 
Thanks guys.

I understand what you mean Rick. The The particular query I have running
compares 3 fields values in one table with 225,000+ records against another
table with 600+ records to look for duplicates.
here is the sql
SELECT VDATAPNC.txtVRM, VDATAPNC.txtVIN, VDATAPNC.txtstatus,
VDATAPNC.txtimportdate, VDATAPNC.txtowner, VDATAPNC.txtmake, VDATAPNC.txtmodel
FROM VDATAPNC, Semita
WHERE (((VDATAPNC.txtVRM)=[semita].[txtvrm]) AND ((VDATAPNC.txtstatus)="01")
AND ((VDATAPNC.txtimportdate)=[semita].[txtimportdate])) OR
(((VDATAPNC.txtVIN)=[semita].[txtvin]) AND ((VDATAPNC.txtstatus)="01") AND
((VDATAPNC.txtimportdate)=[semita].[txtimportdate]));

Thanks for any advice
Tony
 
Hi Rick, Sorry didn't get back yesterday, Sunday and family duties called!
Yes I've indexed the fields that are being compared and the query took 7
minutes to run. The number of records is going to increase so I need to find
a way to run this much much quicker.
Thanks
Tony

Rick Brandt said:
Thanks guys.

I understand what you mean Rick. The The particular query I have running
compares 3 fields values in one table with 225,000+ records against
another table with 600+ records to look for duplicates.
here is the sql
SELECT VDATAPNC.txtVRM, VDATAPNC.txtVIN, VDATAPNC.txtstatus,
VDATAPNC.txtimportdate, VDATAPNC.txtowner, VDATAPNC.txtmake,
VDATAPNC.txtmodel FROM VDATAPNC, Semita
WHERE (((VDATAPNC.txtVRM)=[semita].[txtvrm]) AND
((VDATAPNC.txtstatus)="01") AND
((VDATAPNC.txtimportdate)=[semita].[txtimportdate])) OR
(((VDATAPNC.txtVIN)=[semita].[txtvin]) AND ((VDATAPNC.txtstatus)="01")
AND ((VDATAPNC.txtimportdate)=[semita].[txtimportdate]));

Thanks for any advice
Click to expand...

Are all of those fields being compared as well as the txtstatus field
indexed? I see no reason for that query to not be almost instantaneous.
Click to expand...
 
Hi Mike, yes they are see my reply to Rick earlier. need to quicken this query.
Thanks
Tony
 
Arvin tried Sandra's examples but got a couple of error messages (Invalid use
of Null). Also Ihaving read Rick's post earlier I'm not sure a progress bar
is now feasible?
Thanks
Tony
 
Hi Rick, Sorry didn't get back yesterday, Sunday and family duties
called! Yes I've indexed the fields that are being compared and the
query took 7 minutes to run. The number of records is going to increase
so I need to find a way to run this much much quicker.
Click to expand...

You're using the older style of query where you are relating your tables
in the WHERE clause instead of using the JOIN syntax. Have you tried
doing it the other way? Also what are you doing with this query when you
say it takes 7 minutes to "run"? Is that simply opening the query's
datasheet, opening a form/report based on the query, or what exactly?
 
Hi Rick. The query is the record source for a form. The user clicks on a
command button to open the form. When the query is run the form opens and
shows the duplicates. It takes 7 minutes whether I run the query from design
mode or click on the command button to open the form.

I'm not familiar with JOIN, how does that differ from WHERE? How would it
affect my sql?

I'm tied up from now until 6pm (UK time) so may not be able to get back to
you to comment on any further replies. BUT thanks a lot for sticking with me
on this, really helpful to have someone challenge what I'm doing.

Thanks again
Tony
 
Just one other point Rick, I can't join the two tables because the fields
which I might have used a join on are the fields that I'm searching for
duplicates on, if that makes sense?
Thanks
Tony
 
As Rick said, this should be done in a fraction of a second. I've done more
complex queries that end up opening a form in less than a second (and on a
300 MHz machine).
To beat a dead horse, I missed indexing something because while I had a two
part key which was indexed, I did not have an index on each field in that
key.
 
Hi Mike. Yes I've gone through and indexed all the fields I'm comparing and
still takes 7 minutes. There's obviously something wrong with the sql I think
if as you and others say this should take seconds in spite of the number of
records?
Thanks for the suggestions
Tony
 
Hi Rick. The query is the record source for a form. The user clicks on a
command button to open the form. When the query is run the form opens
and shows the duplicates. It takes 7 minutes whether I run the query
from design mode or click on the command button to open the form.

I'm not familiar with JOIN, how does that differ from WHERE? How would
it affect my sql?

I'm tied up from now until 6pm (UK time) so may not be able to get back
to you to comment on any further replies. BUT thanks a lot for sticking
with me on this, really helpful to have someone challenge what I'm
doing.
Click to expand...

Try the following SQL...

SELECT a.txtVRM, a.txtVIN, a.txtstatus, a.txtimportdate,
a.txtowner, a.txtmake, a.txtmodel
FROM VDATAPNC a
JOIN Semita b
ON a.txtimportdate = b.txtimportdate
WHERE a.txtstatus = "01"
AND (a.txtVRM = semita.txtvrm OR a.txtVIN = b.txtvin)
 
Hi Rick. I've tried that csql and get a error message, "Sntax error in FROM
clause."

Any suggestions? What is the significance of the "a" and "b" I've not seen
that before?

Thanks again
Tony
 
Hi Rick. I've tried that csql and get a error message, "Sntax error in
FROM clause."

Any suggestions? What is the significance of the "a" and "b" I've not
seen that before?
Click to expand...

Sorry, I believe Access requires INNER JOIN instead of just JOIN.

The a and b are just table aliases so I didn't have to type out the full
table name each time and to make the result a bit easier to look at in my
post. You don't have to keep that in there.
 
Maybe I'm missing something, but I would have expected:
FROM VDATAPNC AS a
JOIN Semita AS b

As I understand, a and b are aliases for VDATAPNC and Semita. Rick is far
more knowledgeable than I about such things, so it is certainly possible I
am missing something here, but that is my observation for what it's worth.
 
BruceM said:
Maybe I'm missing something, but I would have expected:
FROM VDATAPNC AS a
JOIN Semita AS b

As I understand, a and b are aliases for VDATAPNC and Semita. Rick is far
more knowledgeable than I about such things, so it is certainly possible I
am missing something here, but that is my observation for what it's worth.
Click to expand...


The As is optional.

Some people like me prefer to use it and others don't.
 
I just learned something else. I haven't seen (or at least haven't noticed)
SQL with aliases that didn't use As, so I didn't know it was optional.
Thanks for the info.
 
Back
Top