MSAccess: Run Stored Proc Asynchronously w/Return - Syntax?

Question

I have had good success in past running single threaded SPs, waiting for a "0" or "-1" reply to indicate success, using:

Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.ConnectionString = "driver={SQL Server};server=" & TempVars!my_ip & ";Trusted_Connection=no;Database=" & TempVars!my_Database & ";UID=username;PWD=password"
cnn.CommandTimeout = 0
cnn.Open

strSQL = "ExportToCSVZip 'myTable', '\\192.168.242.147\InventoryProcessing\Exports\', 'ProdCIVProcess', 'Y';"

Set rs = cnn.Execute(strSQL)

'SP Result -1 = Success, 0 = Fail
If rs.Fields(0) = -1 Then
    msgbox "Done"
Else
    msgbox "Fail"
End If

I can run this asynchronously using:

cnn.Execute strSQL, adExecuteNoRecords, adAsyncExecute

But I am unclear the proper syntax in this form to determine the Return flag. My attempts have returned an 'ordinal' error.

Suggestions?

Possible duplicate of [Assign a stored procedure return value to a VBA variable](https://stackoverflow.com/questions/17868484/assign-a-stored-procedure-return-value-to-a-vba-variable) — June7, Mar 20 '19 at 17:56
@June7 That doesn't answer how to do it asynchronously. Returning a value from an asynchronously ran stored procedure is substantially more complicated. — Erik A, Mar 20 '19 at 19:02
More complicated or impossible. Only thing I can find involves Android https://stackoverflow.com/questions/15739635/how-to-return-value-from-async-task-in-android — June7, Mar 20 '19 at 19:46
Certainly not impossible. Returning values from asynchronously called procedures can be kind-of trivial, that's probably why there aren't a lot of answers about them, but VBA doesn't really do asynchronous stuff so not that much info on it here either. I'll see if I have the time to write up and test an answer later — Erik A, Mar 20 '19 at 20:04
I've managed a small cheat to this problem. The SP creates a temporary file on the FTP site that gets removed JUST before the SP completes. I am testing for the presence of the Temp file - when I find it missing, I consider the SP complete. Not ideal, but seems reliable via testing so far, large and small files. — Mark Pelletier, Mar 20 '19 at 20:33
@MarkPelletier That seems like a pretty horrible workaround. I've shared an answer. — Erik A, Mar 20 '19 at 20:43

Erik A · Answer 1 · 2019-03-20T20:53:51.473

Depending on your exact config, you can return parameters quite easily.

Assume you have the following SP:

CREATE PROCEDURE TestSP 
    @Param1 INT OUT
AS
    WAITFOR DELAY '00:00:03';
    SET @Param1 = 5;

Then, declare globally in your module:

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

And then, in your function:

Set cnn = New ADODB.Connection
cnn.Open = "Some connectionstring"
Set cmd = New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "TestSP"
Dim p As ADODB.Parameter
Set p = cmd.CreateParameter("@Param1", adInteger, adParamOutput, 8)
cmd.Parameters.Append p
Set cmd.ActiveConnection = conn
cmd.Execute Options:=adAsyncExecute

Then, in a separate function, test if the command is done and if so return the value

If cmd.State <> adStateExecuting Then
      returnValue = cmd.Parameters("@Param1").Value 'Returns 5 when done
End If
'Because of the global scope, cleanup is required
conn.Close
Set conn = Nothing
Set cmd = Nothing

Eric, thanks for this outline. I'm not clear on the last "separate function" piece. This is external to the function that calls the SP in the first place, correct? I'm not sure how the 2nd function would have those variables in-scope. — Mark Pelletier, Mar 20 '19 at 21:28
Yup, globals are required to persist the command after the function ends. Did this work? — Erik A, Mar 21 '19 at 06:36

MSAccess: Run Stored Proc Asynchronously w/Return - Syntax?

1 Answers1