Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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 Comments1 Solution1491 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
Avatar of Bitsqueezer
BitsqueezerFlag of Germany image

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

Commented:
This problem has been solved!
Unlock 1 Answer and 19 Comments.
See Answers