Go to Top

Why you should always use a recordset when executing stored procedures

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 Accerss VBA.

Command Object does not cut it

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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 String

strSQL = “Exec usp_TestProcedure 1”
OpenMyRecordset rs, strSQL
With rs
MsgBox “Your procedure returned ” & !MyResult
End With

Start using Recordsets with all of your stored preocedures today!

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

One Response to "Why you should always use a recordset when executing stored procedures"

  • Tony High
    May 23, 2013 - 12:51 am Reply

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]