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

Posted on 2009-02-24
Last Modified: 2013-12-05
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?
Question by:robtroller
    LVL 39

    Expert Comment

    by:Kyle Abrahams

    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.

    LVL 40

    Accepted Solution

    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


    Author Comment

    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.
    LVL 40

    Expert Comment

    by:Vadim Rapp
    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
    LVL 40

    Expert Comment

    by:Vadim Rapp
    further clarification: @rc is a variable in the sql code, not in VBA code.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now