Avatar of mlcktmguy
mlcktmguy
Flag for United States of America

asked on 

How to retrieve returned values from and MS Access Passthru Query , executing a SQL stored procedure

I am a NUB working with a SQL Server back end from withing MS Access 2013.  With help from EE I was able to create some code to invoke a stored procedure and even pass some values from a pass thru query.

With DBEngine(0)(0).CreateQueryDef("")
         .Connect = gConnection
         .ReturnsRecords = False
         .SQL = "EXEC dbo.sptblEventsAdd"
         .SQL = .SQL & "  @EventID    = " & long1
         .SQL = .SQL & ", @PropertyID = " & long2
         .Execute dbSQLPassThrough + dbFailOnError
End With

This worked well ad that SP does not return any values.  When I tried to modify the code to execute an SP that returns one value it didn't work.  The SP would execute but I wasn't picking up the returned value.  My final iteration of code does not execute but give me a message "Cannot execute Select Query".

This is my final iteration of code:
With DBEngine(0)(0).CreateQueryDef("")
         .Connect = gConnection
         .ReturnsRecords = True
         .SQL = "EXEC dbo.spGetNextReceiptNum"
         .SQL = .SQL & "  @MuniCode         = " & muni
         .SQL = .SQL & ", @NewReceiptNumber = " & returnNextReceipt    ' this is returned field
         .Execute dbSQLPassThrough + dbFailOnError
End With

This is the SPROC I want to execute.  It return one int value

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetNextReceiptNum]    Script Date: 4/21/2017 11:01:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            MJO
-- Create date: 01-10-16
-- Description:      Retreive And Update Receipt Number
-- =============================================
ALTER PROCEDURE [dbo].[spGetNextReceiptNum]
      -- Add the parameters for the stored procedure here
      @MuniCode           int = 0,
      @NewReceiptNumber   int = 0    Output
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      Declare @WkReceiptNum      int   = 0,
              @NextReceipt_C  Int   = 0

 -- Retrieve NextReceipt_C

        SELECT
           @NextReceipt_C = NextReceipt_C
        FROM
           tblMuni_Master
        Where
           MuniCode = @MuniCode

    -- Add 1 to the Receipt Number

       SET @NextReceipt_C = @NextReceipt_C + 1
       SET @NewReceiptNumber = @NextReceipt_C

   -- Update the Table

        UPDATE
           tblMuni_Master
        SET
           [NextReceipt_C] = @NextReceipt_C
        WHERE
           [MuniCode] = @MuniCode


END

Does anyone know what my code would have to look like to pass one value and return one value, without getting an error message?
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon