ACC2000: Stored Procedure with Output Parameter Doesn't Return Data

This article was previously published under Q225948
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

When you try to run a stored procedure that uses an output parameter to return data from within a Microsoft Access project (.adp), you are prompted for any input parameter(s) as well as the ouput parameter. After you enter the parameter(s), you receive the following message:
The Stored Procedure executed successfully but did not return records.
To run a stored procedure that contains output parameters from within an Access project, create a second stored procedure that nests the original procedure. Then you can run the new stored procedure instead. The following example demonstrates how to implement this technique:
  1. First, complete the steps in "Steps to Reproduce Problem" section of the "More Information" section later in this article.
  2. Create a new stored procedure, and enter the following text:
    CREATE PROCEDURE OutputSqrrt(   /* Input Parameter used to prompt user for value */    @EnterValue int )AS   /* variable used to return the result */    Declare @OutputResult int    /* Call First Stored Procedure, storing the result in @OutputResult */    Execute Sqroot @EnterValue,  @OutputResult OUTPUT      SELECT  @OutputResult as long					

Steps to Reproduce Behavior

  1. Open the sample Access project, NorthwindCS.adp.
  2. In the Database window, click Stored Procedures under Objects, and then click New.
  3. In a new stored procedure window, enter the following text:
    CREATE PROCEDURE Sqroot   (      @EnterValue int,      @Result int OUTPUT   )AS   SELECT @Result = SQRT(@EnterValue)					
  4. Close and save the stored procedure as "Sqroot" (without quotation marks).
When you run the stored procedure that you've just created, you are prompted for both the input parameter and the output parameter. When you click OK on the prompt for the output parameter, Microsoft Access returns the following message:
The Stored Procedure executed successfully but did not return records.
pra prb doesn t does not

Article ID: 225948 - Last Review: 12/05/2015 13:55:06 - Revision: 2.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbprb kbclientserver KB225948