MS 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

  '(in some startup or initialization rouinte...)
  'create a new instance of an Access application
  'and open the UtilApp
  Set mUtilApp = New Access.Application
  mUtilApp.OpenCurrentDatabase CurrentProject.Path & "\Util.accdb"
  
  'run the utilApp's Initialize procedure, passing this application
  'instance as a parameter via Run()
  mUtilApp.Run "Initialize", 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)

Public Function EventHubCallback(args As Variant)
  
  'this will be called by the utility application as certain processes
  'are completed.  It is essentially the entry point for "events"
  'Bear in mind that these can fire at any unknown time and will block
  'the main UI thread, so keep them short and sweet.
  
  On Error Resume Next
  Forms("Form1").UpdateNotification
  
End Function

Util/Async Processing Application

Option Compare Database
Option Explicit
'(in a standard module so the Initialize function
' can be called from the main application)
Private mHost As Access.Application
Public Property Get Host() As Access.Application
  Set Host = mHost
End Property

Public Function Initialize(obj As Variant)
On Error GoTo Err_Proc
'=========================
  'this procedure is called from the main application
  'and the obj is the instance of that application.
  'We'll hold it here so we can refer to it later to
  'send notifications when certain events happen.
  Set mHost = obj
  
  'this form has our timer code that will check
  'the queue for things to processes.  For the demo,
  'this just enters a Sleep cycle for 4 seconds to
  'simulate a long running process.
  DoCmd.OpenForm "Form1"
'=========================
Exit_Proc:
  Exit Function
Err_Proc:
  Err.Source = "App.Startup"
  Select Case Err.Number
    Case Else
      HandleError
  End Select
  Resume Exit_Proc
  Resume
End Function

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

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Sub Form_Timer()
On Error GoTo Err_Proc
'=========================
  'sleep for 4 out of the 5 seconds to simulate a blocking operation
  Sleep 4000
  Host.Run "EventHubCallback", "some random arg"
'=========================
Exit_Proc:
  Exit Sub
Err_Proc:
  Err.Source = "Form_Form1.Form_Timer"
  Select Case Err.Number
    Case Else
      HandleError
  End Select
  Resume Exit_Proc
  Resume
End Sub