VBA, ADODB - Asynchronous Query Execution with ADODB.Connection Events

VBA doesn't have multiple threads but that's ok because network latent operations such as running queries are packed into libraries which do the multi-threading for you. The ADODB.Connection object that is used to connect to a database can run queries in asynchronous mode with notification of completion implemented with an event if you declare the Connection object with the WithEvents keyword and and supply adAsyncExecute to the Connection's Execute method.

What follows is a code pattern not actual code because I do not know what databases you have installed on your computer dear reader. But what must be stressed is that this is to be placed into a class module (not a standard module). I called my class AsyncQuery

Option Explicit

Private WithEvents cnAsynchronousConnection As ADODB.Connection

Public Sub RunAsyncQuery()
    
    Set cnAsynchronousConnection = New ADODB.Connection

    cnAsynchronousConnection.connectionString = "" '<---- Insert your connection string

    
    cnAsynchronousConnection.Open
    
    Debug.Print "Preparing to execute asynchronously: " & Now
    cnAsynchronousConnection.Execute "<select query>", adAsyncExecute  '<----- Insert you own query

    Debug.Print "Has begun executing asynchronously: " & Now
End Sub

Private Sub cnAsynchronousConnection_ExecuteComplete(ByVal RecordsAffected As Long, _
        ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, _
        ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    Debug.Print "The query has completed asynchronously: " & Now
End Sub

Then in a standard module place the following code.

Option Explicit

Sub Test()
    Dim oAsyncQuery As AsyncQuery
    Set oAsyncQuery = New AsyncQuery

    oAsyncQuery.RunAsyncQuery

End Sub

So without a database we can't take this any further. There are two key points working here, firstly there is the WithEvents keyword in the variable declaration which is only valid in a class module. Secondly there is the flag adAsyncExecute which must be passed to the Connection's Execute method. I have highlighted these key points in bold red.

VBA - XHR - ADODB.Stream - Save a file from the Internet

Another code sample this time to download a file from the Internet using XmlHttp request (XHR) in combination with ADODB.Stream from binary writing to disk.

Option Explicit

'* Tools->References
'MSXML2             Microsoft XML, v6.0             C:\Windows\SysWOW64\msxml6.dll

Private Sub TestSaveFileFromInternet()
    Dim sListOfBanks As String
    sListOfBanks = "https://www.bankofengland.co.uk/-/media/boe/files/prudential-regulation/authorisations/" & _
                    "which-firms-does-the-pra-regulate/list-of-banks-november-2017-excel.xls/"

    SaveFileFromInternet sListOfBanks, "n:\list-of-banks-november-2017-excel.xls"

End Sub

Private Function SaveFileFromInternet(ByVal sUrl As String, ByVal sSaveToPath As String)
    
    Dim oXHR As MSXML2.XMLHTTP60
    Set oXHR = New MSXML2.XMLHTTP60

    oXHR.Open "GET", sUrl, False
    oXHR.send

    With CreateObject("ADODB.Stream")
        .Open
        .Type = 1
        .write oXHR.responseBody
        .SaveToFile sSaveToPath
        .Close
    End With

End Function