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?
Who is Participating?
DhaestConnect With a Mentor Commented:
Perhaps you can add also some DoEvents
(DoEvents yields execution so that the operating system can process other events)
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
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?
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.
All Courses

From novice to tech pro — start learning today.