Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1961
  • Last Modified:

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?
0
robtroller
Asked:
robtroller
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/0f322c7f-9695-42e3-9104-b9c3aa54d5c3/

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.

0
 
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



0
 
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.
0
 
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
0
 
Vadim RappCommented:
further clarification: @rc is a variable in the sql code, not in VBA code.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now