cnA_ExecuteComplete completes at time 8.cnA_ExecuteComplete is still running, so cnB_ExecuteComplete never fires.query A completes first at time 3, cnA_ExecuteComplete fires.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. Sometimes however, one or two of the cnX_ExecuteComplete callbacks don't fire at all. 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.
Typical expected debugger output: Firing cnA query: 10:34:22 )ĭebug.Print "cnC records received: " & Nowĭebug.Print "Sheet3 tables received: " & Now Private Sub cnC_ExecuteComplete(ByVal RecordsAffected As Long. )ĭebug.Print "cnB records received: " & Nowĭebug.Print "Sheet2 tables received: " & Now Private Sub cnB_ExecuteComplete(ByVal RecordsAffected As Long. 'Code to handle the recordset, refresh the relevant presentation sheet here,ĭebug.Print "Sheet1 tables received: " & Now )ĭebug.Print "cnA records received: " & Now Private Sub cnA_ExecuteComplete(ByVal RecordsAffected As Long. You can assume identicalĭim connectionString As String: connectionString = ""ĬnA.Execute "", adAsyncExecute 'takes roughly 5 seconds to executeĬnB.Execute "", adAsyncExecute 'takes roughly 10 seconds to executeĬnC.Execute "", adAsyncExecute 'takes roughly 20 seconds to executeĭebug.Print "Clearing workbook tables: " & Now 'For brevity, only listing set-up of cnA here. Private WithEvents cnC As ADODB.Connection Private WithEvents cnB As ADODB.Connection 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 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. 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 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).
0 kommentar(er)
