Getting a Returned Variable from SQL 2008  into MS Access 2008 ADP Project

Posted on 2012-08-22
Last Modified: 2012-08-25
I'm developing a MS Access 2008/SQL 2008 ADP Project.
I'm attempting to return a string variable (nvarchar (30) from the Stored Procedure (Server Side) to the MS Access (client side) but not having any luck.
Executing the Stored Procedure from the client side is working correctly but I can't figure out  how to return the SP string variable to the client side VB code and in turn using this returned variable with in the VB Code.
I understand that I have to declare an output variable in the SP as @VariableName  Output but I don't know how to return this value to my client side VB calling Execute Stored Procedure command or how to set up a variable in the VB client side code that can accept the Returned variable.    
If any one has an example of the code used on the VB client side Calling Procedure command and Code to set up the Return variable on the SP server side I would be most greatful.
Question by:Adlerm
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    first, clarify that you understand that RETURN value of the procedure is not the same thing as OUTPUT parameter. see here some coding examples.

    next, see this tutorial on how to make this work with ADODB command object, for example:

    the key items there are that you indeed define the parameter with the "direction" of output, and after the .execute, you can grab the parameters value

    Author Comment

    Hi angelIII

    Many thanks for you reply.

    I think I need to clear up a few points - Yes I understand the difference between Return value and Output parameter. I am using Return/Output interchangerable when in fact I should be using Output in this situation.

    Unfortunately all the examples shown (via the supplied hyper links and others I've researched) assume you are in the Server side of things NOT on the client side. For example you don't Declare variables in Access you Dim variables.

    As I said I don't have to worry about setting up connections strings etc from the client side as this has all this  been done and I have no worries about executing the stored procedure from my client side MS Access 2008 ADP database.

    I have attached the code I use to execute the stored procedure (see below) but my concern is how to construct the code to accept a Output parameter from the stored procedure and then to use that output parameter within my client side MS Access VB code.


    Private Sub JobCuttingTemplate_AfterUpdate()

        Dim wsx                                      As Boolean
        Dim sPassedSPReferenceId      As String
        sPassedSPReferenceId = "Exec sp9991_upd_tblJobsSelectedItems" _
                                                    & " " & Me!JobReferenceId  '<== Comes from the current form
        wsx = ADO_ExecCommand(sPassedSPReferenceId)
        'This is where I want to utilise the Output parameter  but don't know how    
    End Sub
    LVL 142

    Accepted Solution

    ADO_ExecCommand => you will need to check out that function, it's not a build-in function.
    so, you will need to check the 2nd link on how to run a ADODB Command object properly in regards to parameters

    Author Comment

    I don't know what you are on about. The above command works and works well. It  is also working in a number of other ADP applications that I've developed. I'm simply trying to establish the correct syntax to use an Output variable generated - passed back - by a stored procedure. I've haven't had the need to to utilise a stored procedure's Output variable before so I'm unsure the of the syntax structure I should use, Everthing I try fails thus my request for help -
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    sorry, but that function is unkown to me, so I cannot tell if/why it doesn't work for you.

    and yes, the function shall work for your current other usages, but not for output parameters, so visibly you cannot use the function as is.

    the code in the link I gave ( is perfectly showing how to do in general. I could suggest a modification of ADO_ExecCommand if I had the source code of that one ...

    Author Closing Comment

    Hi AngleIII
    I'm awarding you the points as you at least attempted to address my problem. In the end I found a work around to my problem that didn't require an Output parameter by the calling command. This doesn't mean that the Output parameter method was wrong - it only means that I couldn't get it to work in this instance so it gives me something to work for just in case I need to use an Output parameter in a future Access/SQL ADP project.
    Again - thanks for you efforts.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    Experts-Exchange users below are the steps you can follow to upgrade your Lync server to latest CU's or cumulative updates. Note: Perform it during non-production hours.   Step 1: Backup your lync and SQL server database. Follow below article: h…
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    731 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

    18 Experts available now in Live!

    Get 1:1 Help Now