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

Posted on 2012-08-22
Medium Priority
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
  • 3
  • 3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38323543
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

ID: 38323710
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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 400 total points
ID: 38323922
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Author Comment

ID: 38325073
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38325280
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 (http://www.freevbcode.com/ShowCode.asp?ID=3687) 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

ID: 38333404
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
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. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

809 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