title: Running multiple async queries with ADODB - callbacks not always firing
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>I rewrote my script as follows (note that I've had to remove the connection strings, query strings etc and make the variables generic):</p>
  
  <pre><code>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 = "&lt;my conn string&gt;"
      cnA.connectionString = connectionString
  
      Debug.Print "Firing cnA query: " &amp; Now
      cnA.Open
      cnA.Execute "&lt;select query&gt;", adAsyncExecute  'takes roughly 5 seconds to execute
  
      Debug.Print "Firing cnB query: " &amp; Now
      cnB.Open
      cnB.Execute "&lt;select query&gt;", adAsyncExecute  'takes roughly 10 seconds to execute
  
      Debug.Print "Firing cnC query: " &amp; Now
      cnC.Open
      cnC.Execute "&lt;select query&gt;", adAsyncExecute  'takes roughly 20 seconds to execute
  
      Debug.Print "Clearing workbook tables: " &amp; Now
      ClearAllTables
      TablesCleared = True
      Debug.Print "Tables cleared: " &amp; Now
  End Sub
  
  Private Sub cnA_ExecuteComplete(ByVal RecordsAffected As Long, ...)
      Debug.Print "cnA records received: " &amp; Now
      'Code to handle the recordset, refresh the relevant presentation sheet here, 
      'takes roughly &lt; 1 seconds to complete
      Debug.Print "Sheet1 tables received: " &amp; Now
  End Sub
  
  Private Sub cnB_ExecuteComplete(ByVal RecordsAffected As Long, ...)
      Debug.Print "cnB records received: " &amp; Now
      'Code to handle the recordset, refresh the relevant presentation sheet here, 
      'takes roughly 2-3 seconds to complete
      Debug.Print "Sheet2 tables received: " &amp; Now
  End Sub
  
  Private Sub cnC_ExecuteComplete(ByVal RecordsAffected As Long, ...)
      Debug.Print "cnC records received: " &amp; Now
      'Code to handle the recordset, refresh the relevant presentation sheet here, 
      'takes roughly 5-7 seconds to complete
      Debug.Print "Sheet3 tables received: " &amp; Now
  End Sub
  </code></pre>
  
  <p>Typical expected debugger output:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Sometimes however, one or two of the <code>cnX_ExecuteComplete</code> 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:</p>
  
  <ul>
  <li>query A, B and C all fire at time 0</li>
  <li>query A completes first at time 3, <code>cnA_ExecuteComplete</code> fires</li>
  <li>query B completes second at time 5</li>
  <li><code>cnA_ExecuteComplete</code> is still running, so <code>cnB_ExecuteComplete</code> never fires</li>
  <li><code>cnA_ExecuteComplete</code> completes at time 8</li>
  <li>query C completes at time 10, <code>cnC_ExecuteComplete</code> fires</li>
  <li>query C completes at time 15</li>
  </ul>
  
  <p>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?</p>
  
  <p>One solution would be to do something extremely quick during the <code>cnX_ExecuteComplete</code> callbacks (eg, a one-liner <code>Set sheet1RS = pRecordset</code> 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.</p>