troubleshooting Question

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

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
19 Comments4 Solutions1491 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
Bitsqueezer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros