Running multiple async queries with ADODB - callbacks not always firing

Question

I have an Excel workbook that fires three queries to a database to populate three tables on hidden sheets, and then runs three 'refresh' scripts to pull this data through to three visible presentation sheets (one per query). Running this synchronously is quite slow: The total time to refresh is the sum of the time of each of the three queries, plus the sum of the time for each 'refresh' script to run.

I'm aware that VBA isn't multi-threaded, but I thought it would be possible to speed things up a bit by firing the queries off asynchronously (thus allowing some clean-up work to be done whilst they were executing), and then doing the population / refresh work for each sheet as the data comes back.

I rewrote my script as follows (note that I've had to remove the connection strings, query strings etc and make the variables generic):

Private WithEvents cnA As ADODB.Connection
Private WithEvents cnB As ADODB.Connection
Private WithEvents cnC As ADODB.Connection

Private Sub StartingPoint()
    'For brevity, only listing set-up of cnA here. You can assume identical
    'set-up for cnB and cnC
    Set cnA = New ADODB.Connection

    Dim connectionString As String: connectionString = "<my conn string>"
    cnA.connectionString = connectionString

    Debug.Print "Firing cnA query: " & Now
    cnA.Open
    cnA.Execute "<select query>", adAsyncExecute  'takes roughly 5 seconds to execute

    Debug.Print "Firing cnB query: " & Now
    cnB.Open
    cnB.Execute "<select query>", adAsyncExecute  'takes roughly 10 seconds to execute

    Debug.Print "Firing cnC query: " & Now
    cnC.Open
    cnC.Execute "<select query>", adAsyncExecute  'takes roughly 20 seconds to execute

    Debug.Print "Clearing workbook tables: " & Now
    ClearAllTables
    TablesCleared = True
    Debug.Print "Tables cleared: " & Now
End Sub

Private Sub cnA_ExecuteComplete(ByVal RecordsAffected As Long, ...)
    Debug.Print "cnA records received: " & Now
    'Code to handle the recordset, refresh the relevant presentation sheet here, 
    'takes roughly < 1 seconds to complete
    Debug.Print "Sheet1 tables received: " & Now
End Sub

Private Sub cnB_ExecuteComplete(ByVal RecordsAffected As Long, ...)
    Debug.Print "cnB records received: " & Now
    'Code to handle the recordset, refresh the relevant presentation sheet here, 
    'takes roughly 2-3 seconds to complete
    Debug.Print "Sheet2 tables received: " & Now
End Sub

Private Sub cnC_ExecuteComplete(ByVal RecordsAffected As Long, ...)
    Debug.Print "cnC records received: " & Now
    'Code to handle the recordset, refresh the relevant presentation sheet here, 
    'takes roughly 5-7 seconds to complete
    Debug.Print "Sheet3 tables received: " & Now
End Sub

Typical expected debugger output:

Firing cnA query: 21/02/2014 10:34:22
Firing cnB query: 21/02/2014 10:34:22
Firing cnC query: 21/02/2014 10:34:22
Clearing tables: 21/02/2014 10:34:22
Tables cleared: 21/02/2014 10:34:22
cnB records received: 21/02/2014 10:34:26
Sheet2 tables refreshed: 21/02/2014 10:34:27
cnA records received: 21/02/2014 10:34:28
Sheet1 tables refreshed: 21/02/2014 10:34:28
cnC records received: 21/02/2014 10:34:34
Sheet3 tables refreshed: 21/02/2014 10:34:40

The three queries can come back in different orders depending on which finishes first, of course, so sometimes the typical output is ordered differently - this is expected.

Sometimes however, one or two of the cnX_ExecuteComplete callbacks don't fire at all. After some time debugging, I'm fairly certain the reason for this is that if a recordset returns whilst one of the callbacks is currently executing, the call does not occur. For example:

• query A, B and C all fire at time 0
• query A completes first at time 3, cnA_ExecuteComplete fires
• query B completes second at time 5
• cnA_ExecuteComplete is still running, so cnB_ExecuteComplete never fires
• cnA_ExecuteComplete completes at time 8
• query C completes at time 10, cnC_ExecuteComplete fires
• query C completes at time 15

Am I right in my theory that this is the issue? If so, is it possible to work around this, or get the call to 'wait' until current code has executed rather than just disappearing?

One solution would be to do something extremely quick during the cnX_ExecuteComplete callbacks (eg, a one-liner Set sheet1RS = pRecordset and a check to see if they're all done yet before running the refresh scripts synchronously) so the chance of them overlapping is about zero, but want to know if there's a better solution first.