Animated icon 2

[jopaumier]

My VB program uses SQL to retrieve information from several tables from an Access database and combines it with a couple other fields from two tables in a second Access database. Depending on the size of the first Access database and the speed of the users machine, this process can take from 5 seconds to many minutes (20-30 possibly). I don't want a user to think the system is hung after 15 minutes when it is still just crunching away (something we noticed and a beta tester commented on). A simple message to 'please wait while the table is created' is not sufficient to let the user know all is proceeding. I've added an animation control that displays while the program works away. If the program really does 'stop responding' and gets hung, does the animation stop as well? If the animation continues if the program is hung, then I don't see a compelling reason to include the animation. What else could accomplish this feedback to the user?

thanks,
Jim

 

[AndyWatt]

I tend to use a ProgressBar or percentage counter as sometimes an animation can continue playing even though the process that started it running has hung. Witness Windows' own behaviour sometimes...

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[AkutaSame]

When VB is doing heavy SQL/Database accesses, it causes the refresh of the form to not work correctly. It doesn't refresh the window, and the form becomes "white." A progress bar MAY help with this, but in the end it may still not fix the issue. You may want to try forcing a refresh of the form inside the loop that is looping through the recordsets (I just thought of this, and will be testing it later this week).

 

[jopaumier]

Thank you both Andy and Akuta. Seems like it should be a simple problem to solve, but when I think about the operation of a commercial Windows product, I do sometimes wonder if the program hasn't hung up somewhere. Akuta, let me know what you find out with your test. If I am rolling through a table one record at a time (without using a SQL query), I update visual counters and throw in a DoEvents whenever the counter updates (every 100-1000 records, depending on what the program is doing).

Jim

 

[AndyWatt]

What about putting a DoEvents inside a Timer's Timer event? I don't have a large enough database to hand to test this on.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[jopaumier]

Andy,

Not sure about it. I will have to do more thinking on this subject. I think I might have to combine a couple thoughts from the other forum discussions (thread222-638102, thread709-241135).

Thanks for your repsonses
Jim

 

[AkutaSame]

I work regularly on a 100,000+ record database. Creating any sort of animated image will not work unless you can force the form to refresh. I haven't tried to force the refresh during the loop in which one goes through a heavy load of database interaction yet, but I will be doing it shortly. You could always beat me to the punch to see if it fixes the problem, jopaumier

Just add FormName.Refresh into the loop.

 

[jopaumier]

AkutaSame,

I doubt if I'll beat you to the punch. Since it is a SQL statement, trying a form refresh probably isn't possible. Actually it's about 4 separate SQL statements, so I can change the icon between SQL statements. That may be the best I can really do. It'll probably be this weekend before I'll do any further exploring on this topic (the two threads I mentioned earlier). I may just resort to simple animations and hope that the program never hangs but the animation keeps going.

Thanks again, and should you come across something, let all of us on the forum know.

Thanks,
jim

 

[CCLINT]

If you use Async processing of the recordset, and have declared the recordset object at form module level using WithEvents, then not only will the recordset run Async, on a seperate thread, but it will also raise a FetchProgress event periodically, depending on the FetchSize, and once done, the FetchComplete will fire.
In these events you can update a form's progress indicator.

You can also use the Recordset's State property and see if it returns a value of (adStateOpen + adStateFetching), with or with-out the use of a Module level recordset object variable declared using WithEvents...You just will not have the events available.
This could be checked in code or in a timer, turning the timer on and off.

If you are using the connection's Execute method to perform action queries, or to fill a recordset, you can also run this Async (adAsyncExecute).
While the connection object does have a ExecuteComplete event, which will fire once the Execute is complete, it does not have a ExecuteProgress event.

But, it does have a property called State, and two available return values for this property are adStateExecuting and adStateFetching, which also can be used to switch off a timer.

Whether you run the recordset opening, or an action query, in order for the application to receive adequate processing time, during the ado execution, in order to refresh your form, you will need to run the process on a seperate thread, which is what the async methods do...

 

[vb5prgrmr]

If you really want to test to see if the animation will continue to play then start a new project add an animation control and set its autoplay to true and give it an animation to play. Then add a command button, the Sleep API and in the click event of the command button add...
[tt]
Sleep 10000
[/tt]
run, click observe...

good luck

 

[jopaumier]

CCLINT,

Thank you for the information. I will admit that I do not understand some of it (e.g., asynchronous processing of the recordset and threads), but I can find and learn more about it. I am familiar with other aspects of your response (e.g., the State property, but not all the possible values). Hopefully I can apply it over the next month, as time permits, and before the update is due.

Thanks again,
Jim

 

[jopaumier]

vb5prgrmr,

Your response came in as I repsonded to CCLINT's reply. Thanks for the suggestion.

Jim

 

[CCLINT]

vb5prgrmr, yes of course, good point, and of course that is what the thread title is about....

I was more interested to answering:
"What else could accomplish this feedback to the user?"
and
"...I tend to use a ProgressBar or percentage counter "

The ability to use a progress bar for a recordset completion status, or at least a visiual indication that the recordset is actually still "working" and not hung up, is available with ADO when using async processing.

Still, the animation is a good to use for everything other situation, ADO/RDO or not.

 

[vb5prgrmr]

Oh, agreed! But I have been reading this thread for the past couple of log-ins and what I was getting out of the later replies is how to test if the animation would still work if the program was hung. Well it hit me after I logged off last night/this morning on how to test it. So when I saw that this thread was still active, I added my .02 and then read your post, nice info... It then dawned upon me as I wake up after only a few hours that this thread222-668452 may be of interest to jopaumier and AkutaSame. It is about doevents, Me.Refresh, and Control.Refresh and the speed of loops and updating users to the progress of heavy processing.


Oh yeah! and you can modify my suggestion to add a timer with a counter and a display of some sorts to see if the timer fires when the simulation goes on.

Good Luck

 

[jopaumier]

Thanks guys. Didn't mean to spur this much activity. I guess I am trying to look at it from two points of view: what (little) I do know and what I found on this forum, as well as if there was something that I had not found that would be better. So I think both CCLINT and vb5prgrmr hit the two-headed nail on the head(s). I appreciate all the advice. I will digest this information and come up with a solution to my situation.

It's a nice day in the Piedmont of North Carolina, so I think I will take the rest of the day off and enjoy the sunshine.

Jim

 

[CCLINT]

Thanks jopaumier!

How about then marking vb5prgrmr post using
"Mark this post as a helpful/expert post!"
so other members can easily spot this as one of the possible answers, depending on what is needed?