Screen freezes on recursive reading of a database

I have an application in VB6 that recursively loops through a large Access database and updates a field. I have a Progressbar on the screen as well as a text box that displays the record being updated. I have a Screen.Refresh command that executes every 100 loops. Everything works perfectly for the first approximately 40,000 loops (it is a different point every time) at which time the screen stops updating. The program continues to rum and in fact completeds the task successfully. The problem is that once the screen stops refreshing I can no longer see the progress being made. Any ideas?
cdvAsked:
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.

DhaestCommented:
Perhaps you can add also some DoEvents
(DoEvents yields execution so that the operating system can process other events)
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
23637269Commented:
I would have the same problem from time to time with very large recordsets.
Make the control that is being updated invisible,, and remove any refresh or doevent or anything else that you can.  You want the code in the loop to be as clean as possible.

I would normally add a lable or something behind the control that would say something like "Processing Request..." or something like that to let the user know that computer is working and not locked up.  When  you set the grid's visible property  to false or whatever the control is that is receiving the data the message would be displayed.  Then when finished set the visible back to true.

If you post the code we can try to optimize it as much as possible.

Thank You
Roger
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>recursively loops through a large Access database and updates a field
Just to 'check the box', is there any way this recursive function can be re-written to a single query, with whatever subqueries are necessary?
0
cdvAuthor Commented:
Thank you DHaest. This has solved the problem 100%. I have removed all Form.refresh instructions and replaced them with a single DoEvent instruction that executes every 200 loops.

Thank you Roger for your suggestion and offer to look at the code. As it happens I do display a label that says "Please be patient". The problem is that the routine runs for a full hour and I want the user to get an idea of the progress. The accepted solution does that.
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 Classic

From novice to tech pro — start learning today.