I’ve talked about using a recordset with stored procedures before, but this time I wanted to emphasize the importance of always using a recordset when executing a stored procedure from Access VBA.
Command Object does not cut it
Usually a programmer will use a command object to execute a stored procedure that does not return records. The format is:
ExecuteMyCommand “Exec usp_TestProcedure 1”
Where the procedure name is usp_TestProcedure and the number 1 is an argument. ExecuteMyCommand is from my EasyADODB library. The problem with this approach is the lack of feedback you get back from SQL Server: Did the procedure execute ok? Where all the transactions completed? There is no easy way to return the results back to Access unless you use additional code.
Use a Recordset Instead
When you use a recordset to execute the stored procedure, you can easily return values back from your stored procedure and determine if all is well. First, here is the stored procedure we are going to use for testing:
CREATE PROCEDURE usp_TestProcedure
@TestError Int
AS
IF @TestError = 1
Select 1 AS MyResult
ELSE
Select 0 AS MyResult
END
usp_TestPrcedure will return a recordset with just one field: MyResult. Here is an example that returns 1 from it:
Dim rs As ADODB.Recordset
Dim strSQL As StringstrSQL = “Exec usp_TestProcedure 1”
OpenMyRecordset rs, strSQL
With rs
MsgBox “Your procedure returned ” & !MyResult
End With
Start using Recordsets with all of your stored procedures today!
I like to use an output variable and then fill it with ERROR_MESSAGE() so that I can then display the actual error. Otherwise I am troubleshooting in the dark. It adds an extra step to your command but it gives you all the info you need or you can even put in your own message.