Help! Access isn't responding during long calculations...

[AaronStewart]

I'm building an Access application that does a lot of processing when the
user clicks a button -- typically 10 minutes' worth or more!

I've set up the status bar to show progress. This is important to me
because of the long running time of the program.

The problem is that after it runs for a few seconds, the user interface
stops responding (including the status bar). The program continues to run,
but it doesn't repaint the window until it finishes.

I've included a "Repaint" command in the processing loop, but this still
doesn't keep Access from going off into never-land.

Any suggestions?

Thanks in advance,

Aaron Stewart

 

['69 Camaro]

Hi, Aaron.

The problem is that after it runs for a few seconds, the user interface
stops responding (including the status bar). The program continues to run,
but it doesn't repaint the window until it finishes.

Relinquish control to the operating system periodically so that these
"cosmetics" can be attended to. Place the DoEvents function at appropriate
places between executable lines of code, so that the screen can be updated
periodically. Perhaps between updates to the status bar would be
appropriate. Be aware that relinquishing control to the operating system
means that the operating system will typically execute other tasks waiting in
the queue, including the next screen update, so this long running procedure
will take a little longer.

' (In a loop
rtn = SysCmd(acSysCmdSetStatus, "Processing item #" & idx & " of " &
recSet.RecordCount)
DoEvents
' Executable code here.
' (End of loop.)

However, if the entire ten minutes is for a single line of code being
executed, then this method won't work. There's nothing you can do while the
code runs if the code can't relinquish control to the operating system before
completion.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[David C. Holley]

Sometimes it just takes some time for Access to do what it needs to do.
One alternative is to create a form which is displayed when the
processing starts on which you have a text box that you update to advise
the user of the progress. Yes its similar to using the Status Bar, but
its much bigger and obvious. In you code, you'd open the form and then
at key points add

[Forms]![frmAdvisory]![txtAdvisory] = "Starting..."
[Forms]![frmAdvisory].Repaint

[Forms]![frmAdvisory]![txtAdvisory] = "Cleaning up files..."
[Forms]![frmAdvisory].Repaint

 

[David C. Holley]

Oh and I would review the logic to ensure that its already optimized.