robtroller
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
further clarification: @rc is a variable in the sql code, not in VBA code.
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.