Using a Progress Bar For A Long Query

I have a revenue archive application that pulls in data from a flat file and converts it. There are several steps in the process:

1) The flat csv data file is saved as an Excel file that is linked to the application. All the values in this file are text fields.

2) The Excel data is imported into a working file inside the database.

3) The data in the working file is tested against 6 "standing tables" that contain the desired revenue tags and their key fields. Any new items are appended to these tables and assigned new numeric keys.

4) Once there is a relational key record for every value in the import file, the records in the import file are distilled down from their text values to their relational keys and stored in the master archive file.

Each of these steps takes a while and I would like to use a progress bar to give the user an idea of where we are in the process. But step 2, for example, is just an append query that is running on 20-30,000 records. So I can't figure out how to measure the progress of that query's execution so I can assign it a value for use in advancing the progress bar.

Any ideas?

Thanks!
Buck_BeasomDatabase DesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
I dont think you can use a progress bar with a query because you would only know of its status when its completed.
0
Hari1612Commented:
You can create a progress bar.. if have a vba code to do your actions record by record. In that way, you can have a count of total records and provide a progress bar and update by a % of overall progress.

You can also provide the user with which step it is in as per your 1..2..3.. listed abvoe.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Avinash DesaiSr Software EngineerCommented:
Hi

Use Background worker component to process it and get the status for the progress bar . if you are developing in windows app.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.