MS Access freezes while running VBA, then unfreezes once finished

Question

I've got a VBA script that loops through a table and it works fine except that while it runs the code Access freezes. Once the code is run, it unfreezes and everything as it showed be (the vba performed the required actions, it opens up a final query as coded etc.)

This was fine up until now because I've implemented (or want to implement) a "progress status" function of the VBA script. This is because due to new requirements the table being analyzed jumped from about 4,000 records to about 50,000 records.

I've done this by using a simple modal dialog form that refreshes after each record.

The Modal Dialog is initially opened:

DoCmd.OpenForm ("ProgressReport")
Forms("ProgressReport").Controls("ProgressLabel").Caption = "0 of " & RecordCount & " records analyzed."

Then after each iteration:

RecordsAnalyzed = RecordsAnalyzed + 1
Forms("ProgressReport").Controls("ProgressLabel").Caption = RecordsAnalyzed & " of " & RecordCount & " records analyzed."
Forms("ProgressReport").Repaint

Is it possible to allocate 'memory' to the modal form so even if Access freezes, this is kept alive? The code is slightly complicated but its just Do Until (EOF) and If Statements - pretty basic VBA functions. Any thoughts on how I can get this progress bar to work? Without or without the Modal Dialog.

I've looked into editing the Status Bar however, that won't work either given Access freezes.

score 4 · Accepted Answer · edited May 23 '17 at 11:49

4

What you need is the DoEvents statement.

What this does is let other items in the CPU queue run. This will slow down your app, but you'll get control of your application back.

Put the command inside one of the loops.

Btw, it's easy to overuse DoEvents. This SO post will tell you more.

edited May 23 '17 at 11:49

Community

1
1

answered May 15 '13 at 06:25

Tom Collins

4,069
2
20
36

score 1 · Answer 2 · answered May 15 '13 at 16:14

DoEvents, to give Windows an opportunity to update the display should be useful, as Tom mentioned. Limit the performance impact by not calling DoEvents for each of 50K rows. Instead choose some reasonable interval, say every 100 rows, and update the progress at that interval.

Consider adapting the strategy outlined in this skeleton code. I just used Debug.Print to show progress.

Public Sub ShowProgress()
    Const lngInterval As Long = 100
    Dim RecordsAnalyzed As Long
    For RecordsAnalyzed = 1 To 500
        If RecordsAnalyzed Mod lngInterval = 0 Then
            ' update progess here ...
            Debug.Print RecordsAnalyzed & " records analyzed"
            DoEvents
        End If
    Next
End Sub

Good answer. I was too tired last night to give much detail. — Tom Collins, May 15 '13 at 16:17

MS Access freezes while running VBA, then unfreezes once finished

2 Answers2