Pass SQL 2005 Stored Procedure Return Value To Access 2007 .adp Project VBA Code

I am trying to capture the return value from a stored procedure in an Access 2007 .adp project.  I've been beating my head against a wall for days trying everything I have found online with no success.  Can anyone send me a simple example of a stored procedure and the VBA code that would capture the value returned by a Return @@error statement?
Who is Participating?
Vadim RappCommented:
Sub main()
CurrentProject.Connection.Execute "create PROCEDURE sp1 AS RETURN 777"
    Debug.Print CurrentProject.Connection.Execute("declare @rc int;exec @rc=sp1;select @rc")(0)
currentproject.connection.execute "drop procedure sp1"
End Sub

Kyle AbrahamsSenior .Net DeveloperCommented:

The solution is normally to create an output parameter and then select your return codes you want (@@error, @@rowcount, @scope_identitty, etc) into that parameter.

robtrollerAuthor Commented:
I understand the stored procedure part.  Its the VBA code that calls the stored procedure and stores the return value in a variable that is causing me headaches.
Vadim RappCommented:
the easiest way to return something from ADO object is by using recordset. Can use parameters as well, but would require much more code. So, we use the fact that construct exec @rc=sp1 returns return code of the stored procedure into the variable @rc; and then we convert that into the recordset by issuing select @rc
Vadim RappCommented:
further clarification: @rc is a variable in the sql code, not in VBA code.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.