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

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.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
AdlermAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

AdlermAuthor Commented:
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 -
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 ...
AdlermAuthor Commented:
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.
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.