In Promises in VBA I introduced how to use promises in VBA to orchestrate asynchronous activities. One of the things that you can do in VBA asynchronously is to get data from a workbook or a database using ADO. It’s very complex to orchestrate and especially so asynchronously. I also found some issues about the connection slipping out of memory in Excel 2010, so this is very much alpha.
I provide an example promise enabled class in the downloadable promises.xlsm workbook that will handle all that. Here’s how to load data asynchronously from a workbook into a sheet in the current one.
' we'll do an async ado copy from one sheet to another
With loadUsingADO(register, "sourcedata", "scratch")
.done callbacks, "copyFromRecordset"
.fail callbacks, "show"
' get the memory back from the ADO opeation
.done callbacks, "tearDownADO"
End With
This will call a function loadUsingADO (that we’ll look at in a moment) which returns a promise that it will load data as requested, and immediately gives back control to the caller. Later on, when its done, it will use callbacks.copyFromRecordset (we’ll look at that later too) to copy the retrieved data to a worksheet, and then it will try to clean up any memory consumed by ADO using callbacks.tearDownADO. Finally, if it all fails, it will use callback.show() to deal with and report on the error.
loadUsingADO function
Private Function loadUsingADO(register As cDeferredRegister, _ sheetFrom As String, sheetTo As String, _ Optional source As String = vbNullString) As cPromise ' we are going to kick off an async sql query and return a promise to it Dim cstring As String, sql As String Dim ad As cAdoDeferred Set ad = New cAdoDeferred register.register ad ' just take it from this workbook If source = vbNullString Then source = ThisWorkbook.Path & "" & ThisWorkbook.Name sql = "select * from [" & sheetFrom & "$]" ' connection string for excel 2007 cstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ & source & ";Extended Properties=""Excel 12.0 Macro;HDR=YES"";" Set loadUsingADO = ad.execute(cstring, sql, Array(Range("'" & sheetTo & "'!a1"))) End Function After creating a new cAdoDeferred class (an example i provide)
- registers itself to avoid it slipping out of memory during the asynch operation.
- sets up the connection string – in this case to an Excel workbook
- calls ad.execute() with the connection string, the sql to execute and an additional argument to be returned eventually to the .done() method of the promise() it returns.
- returns the promise back to the caller, who will use it to attach .done() and .fail() actions to.
cAdoDefferred class
This is an example of the kind of promise class you would implement for your own use. Its purpose is to manage the mechanics of ADO asynch and eventually resolve or reject an associated cDeferred(). Luckily, ADO comes along with a set of events that can be hooked into. Option ExplicitPrivate prDeferred As cDeferredPrivate prOptionalArgument As VariantPrivate prSql As StringPrivate WithEvents prConnection As ADODB.connectionPrivate WithEvents prRecordset As ADODB.RecordsetPublic Property Get deferred() As cDeferred Set deferred = prDeferredEnd PropertyPublic Property Get optionalArgument() As Variant optionalArgument = prOptionalArgumentEnd PropertyPublic Function execute(cstring As String, sql As String, _ Optional a As Variant) As cPromise ' this is an optional argument that can be passed to the resolution callback ' recommend that this is wrapped in an array to avoid set/not set problems prOptionalArgument = a prSql = sql Debug.Print "im opening a connection" ' set up connection attributes and open asynchronously With prConnection .CommandTimeout = 60 .ConnectionTimeout = 30 .ConnectionString = cstring .CursorLocation = adUseClient .Mode = adModeRead .Open , , , adAsyncConnect End With ' this will get resolved in the async part Set execute = prDeferred.promise End FunctionPrivate Sub Class_Initialize() Set prDeferred = New cDeferred Set prConnection = New ADODB.connection End SubPrivate Sub prConnection_ConnectComplete(ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.connection) ' fired when asynch connection has been made - now to get the data Debug.Print "connection complete" If adStatus = adStatusOK Then With pConnection .execute prSql, , adCmdText Or adAsyncExecute End With Else ' failed to connect - mark promise as rejected prDeferred.reject (Array(pError, "failed")) End IfEnd SubPrivate Sub prConnection_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) ' fired when sql has been executed Debug.Print "connection Execute complete" If adStatus = adStatusOK Then prDeferred.resolve (Array(pRecordset, pConnection, optionalArgument)) Else ' failed to connect - mark promise as rejected prDeferred.reject (Array(pError, "failed")) End IfEnd Sub There are actually 2 asynchronous things happening here.
- The connection will fire the event ConnectComplete when done
.Open , , , adAsyncConnect
- When ConnectComplete gets fired, assuming everything is good, it will kick off a second async process,
.execute prSql, , adCmdText Or adAsyncExecute
- When that’s done it will fire ExecuteComplete. That’s where we signal that the whole thing worked or not, and also pass the result (via the deferred.promise()) object.
If adStatus = adStatusOK Then prDeferred.resolve (Array(pRecordset, pConnection, optionalArgument)) Else ' failed to connect - mark promise as rejected prDeferred.reject (Array(pError, "failed")) End If Back in the main calling program, the promise will be signalled to check its promise status and either execute .done() or .fail(). (There can be many .done and .fail actions. They will all get executed whenever the promise is completed. If a .done() is requested against a promise that has already been fullfilled, it will execute immediately.) That’s the end of the role played by the cAdoDeferred class
Executing .done()
Back in the calling program, which has been ignoring all this activity and perhaps getting on with something else, (or maybe even exited), promise.done() is signalled that it should execute. .done callbacks, "copyFromRecordset" Because of VBA constrainsts, the only way I could think of to execute this is to create an instance of a class which I’ve called yourCallBacks, where each method is some processing you want to execute following a .done() or .fail() signal. Earlier we created an instance of yourCallBacks Dim callbacks As yourCallbacks Set callbacks = New yourCallbacks It’s in this class that you would create the processing to be performed following a done signal. In this case we have asked for callbacks.copyFromRecordSet() to be executed. Its purpose will be to do something with the recordset that has just been retrieved asynchronously by cAdoDeferred Here’s the code Public Sub copyFromRecordset(a As Variant)' arguments wrapped in array Dim data As ADODB.Recordset, r As Range, i As Long, ws As Worksheet Dim lb As Long ' establish where arrays start lb = LBound(a) ' get what was passed as part for the resolution Set data = a(lb + 0) ' this was passed as an optional argument Set r = a(lb + 2)(lb) Set ws = r.Worksheet ' populate the range ws.Cells.ClearContents ' headers For i = 0 To data.Fields.Count - 1 ws.Cells(1, i + 1).Value = data.Fields(i).Name Next ' copy data ws.Range("A2").copyFromRecordset data Debug.Print ("i've done the async ado")End Sub
Dealing with arguments from deferred.resolve()
When this promise was resolved, data was passed to it – in this case the recordset, the connection and some optional argument that was provided at instantiation time. prDeferred.resolve (Array(pRecordset, pConnection, optionalArgument)) Although not necessary (since you control what gets passed and what to do with it), I wrap this in an array. This is to avoid having to figure out what type the data is – since some types would have be handled differently. The promise is just a pass through for the data – it doesn’t need to know what it is, so its always better just to wrap the arguments in an array. I also apply the same logic to the optionalArgument – so in fact the optionalArgument is itself an Array of other kinds of data. In the handler, all that can be dealt with easily and properly typed. I want to pick out the recordset (the first argument) and the target range (the first element of the array that is the 3rd argument) Dim data As ADODB.Recordset Dim lb As Long, r as Range ' establish where arrays start lb = LBound(a) ' get what was passed as part for the resolution Set data = a(lb + 0) ' this was passed as an optional argument Set r = a(lb + 2)(lb) Finally I want to clear up memory a little since we’re done with all the ado stuff. Back in the caller I add a second .done() .done callbacks, "tearDownADO" which executes this. Note that we use the same logic as before for dealing with the arguments. This time extracting the first (the recordset) and the 2nd (the connection) Public Sub tearDownADO(a As Variant) ' clean up after an ADO ' arguments wrapped in array will be Dim data As ADODB.Recordset, connection As ADODB.connection Dim lb As Long ' establish where arrays start lb = LBound(a) ' get what was passed as part for the resolution Set data = a(lb + 0) Set connection = a(lb + 1) ' close the record set & connection data.Close connection.Close Debug.Print "ive closed the recordset"End Sub All this seems pretty complex at first, but the benefit is that I have a very simple calling procedure, resusable asynchonicty, and no global variable synchronization. I’ll never have to worry about dealing with ADO events again. For more on this see Promises in VBA. Since this is in the early stages of development the code is in a separate workbook (promises.xlsm) and can be downloaded here