MS Access Progress Bar

[drjez]

Hi guys,
I know how to use progress bar when you have more than 1 command or recordset (you know the number or records before you start progress bar). My problem is that I'm not looping through a recordset, I'm running a canned
query (max 2 min.) - 1 command. No code appears to execute while the query is running, so I don't know how to make the progress meter increase. If I just start it before the query is executed, all I can do is move the meter to 100% after the query finishes executing. That's why I tried the timer event, but it doesn't run, either. I have put this problem in different forums, but no answer. I don't believe that this is not possible.

Thanks,
dj

 

[The_Doc_Man]

I don't believe that this is not possible.

Doesn't matter what you believe. Does Access think it is possible?

I'm running a canned query

And the answer is ... not unless the query is running on a back-end server (and I do NOT mean as a file-server-resident back-end. I'm talking MySQL or SQL Server or ORACLE or something similar.)

But why is this so? Because ...

A canned Access query does not include "events" within itself. It is a single-threaded object that, when you run it (say, with ExecuteSQL or RunQuery or another similar method), runs as a black box.

To drive a progress bar, you need to get enough information out of the process to update the progress bar. To get information out of a black box, you have to have a way inside - a way to crack the box - which Access does not give you.

So if this is a query on a shared but otherwise pure Access application, you cannot do what you want. You have nothing to see. No events will break execution for you. A timer won't help because the query has the table or tables locked. If you try to run a second query from the timer, you will run into some sort of lock problem. Worse, you might give the main query the problem if your "evaluate progress" query gets there first for the next record to be processed within the query. Can you say "query failure?" I knew you could.

On the other hand, if you are running queries on a DB-server back-end, that kind of setup can be multi-threaded and sometimes you can do a "progress-check" query. In which case, your timer would work reasonably well.

See, here's the thing to remember about Access. It is a SMALL-business database tool. Which includes that the base product is not inherently built to be multi-threaded even though Windows supports that ability. Without an intervening event to provide you the "window" into the query's progress, you have nothing on which to base your progress meter.

Just for clarification, when you have a back-end or shared Access DB that many users can open at once, that IS multi-threaded. One thread per desktop, 'cause that's where the threads run. The file server code is ALSO multi-threaded - but it ain't Access. It's just a file-sharing program.

Now... having burst your bubble, how can you possibly do this anyway? Why, bless you, I thought you'd never ask.

If this is a canned query but you have control over how it is run from VBA code, perhaps as a button-click event, break it up into a convenient number of phases. Like, subdivide the range of the query based on letters of the alphabet that start the prime key if it is alpha, or based on subsets of the range of the record keys if numeric. Change it to a parameter query and make the starting/ending keys (i.e. the range) your parameters. Then update your progress meter in the loop that drives the subset queries based on the range subset passed in as parameters. Run the loop to step through all possible range elements.

If you have no control over the query because of policy, permissions, or other impedimentia, you're stuck. Then, you REALLY can't do what you want.

 

[drjez]

Vb?

Thank you for an explanation !

I am running stored procedure on SQL Server using ADO. Access and SQL Server are installed on the same machine. But as I understand it is not possible anyway unless I split the query ...
I could do the same thing using Visual Basic 6.0 instead of MS Access. Coding is similar and I have some experience with VB also. Do you know whether it would be possible in VB? Is VB multi-threaded? What would you recommend.

Thank you very much and I appreciate your help.
dj

 

[drjez]

Message for Doc Man

Hopefully you will get back to this forum and give me the answer or somebody else?

So in Access it is not possible to use progress bar the way I described. Is it possible to use VB6? Does a tool have to support multi-thread technology?

thanks
dj

 

[The_Doc_Man]

Never tried this with VB, so can't answer directly. The last time I had a problem like this, I did it via VBA anyway. I could not let the query run uninterrupted, so the progress bar was easy to maintain. I had to do the equivalent of the query one record at a time because I was rolling my own "forensic audit" log behind the scenes. Not necessarily the same for you, so the solution doesn't translate as well.

If you are using SQL server AND there is a simple "metric" you can sample that doesn't involve database locks, you can trigger one query that runs asynchronously and trigger the other query through timer code. BUT... you have to pay close attention to locking considerations anyway. Further, if the "metric" you would test requires you to actually look at the individual records in some detail, you could have a serious lock-conflict problem even for this case.

Based on that line of thought, VB vs. VBA doesn't help because if you are still using SQL server behind the scenes, it won't matter even a smidgeon what your VB does. SQL server will still have locks on everything while the query runs. Again, this is the "black box" issue. Without feedback from the box itself, you are kind of stuck.

Maybe you should just run a "cylon" display like the moving red lights on the robot helmets on the (old) Battlestar Galactica series to let folks know their terminals are still running... you could do that with a timer routine pretty easily. If you had speakers, you could even make them do an electronic howl now and then for giggles.

Seriously, I don't see an easy answer that doesn't involve changing the query to meet this other need. And I would hate to break something so good as a perfectly working query just 'cause some of my users get antsy in their pantsies.