MS Access

Async Processing in Access

I’ve been wondering for a while how to go about running async tasks within Access.  The lack of multi-threading support has always been something I wished was available, but not until relatively recently when dealing with Azure backend databases has this lack of feature been quite so painful.  While there’s typically plenty of ways around it, it would certainly be helpful to have in many cases.

In any case, I recently came up with a way to implement some rudimentary async processing by using a separate utility Access application that is fully managed by the main front end.  Commands can be sent to the async processor from the main application, and when completed (or at other desired intervals/events) the async processor sends a notification back to the main application.

Naturally there’s restrictions – the two aren’t “tightly tied” to each other and the only interface is through these command/notification methods (and any shared linked tables), but it’s enough to empower some pretty good functionality overall.  The restrictions aren’t much different from multi-thread programming in other platforms, so it should be no biggie.

Overall, it’s a considerably more friendly setup than tapping into the .NET framework with COM interop and wired events.

Attached is a zip file with a demo.  Be sure both Main and Util applications are in trusted folders!  Otherwise you’ll get an error when trying to instantiate the util app that does the async processing.

Open the main app and open Form1 to see it in action.  This uses a 5 second interval timer for the async process and simulates a long running task with a 4 second Sleep command.

Here’s some basic (pertinent) code, but the demo has everything wired up nicely.

Main Application

(the callback for the notifications – make sure it’s in a standard module and public so the util/async app has visibility to it)

Util/Async Processing Application

(and the form with a timer to simplify the simulation of a long running task)

Highlight the Current Row

Using a continuous form, Conditional Formatting and VBA, we can easily give a visual indicator of the currently selected row. This example sets the ID field as Bold for the current row, but this can be applied to multiple controls. A common approach is to make a textbox that fills the background of the continuous form, bring all of the other controls on top and set their backgrounds to transparent so the underlying “coverage” control shows through, then just highlight that control.

Use the Current event to remove existing FormatConditions then create a new FormatCondition based on the current record’s ID and apply it to the control:

That’s it!