Pass-through Query to SQL Server

[anissaw]

Good afternoon -
I have looked at your forum to try and get ideas of using pass-through query. I have setup a simple stored procedure and need to get it executed from Access as well as restrict based on the parameters of my Access form. My form has 3 text box fields that the data needs to limit to (startdate, enddate, clientname, site). Below is my stored procedure:
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[pt_VemmaInsert] Script Date: 12/22/2015 1:46:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[pt_VemmaInsert]
AS
DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
DECLARE @OWNERB varchar(Max);
DECLARE @SWHSB varchar(Max);


set @StartDate = @StartDate
set @EndDate = @EndDate
set @OWNERB = @OWNERB
set @SWHSB = @SWHSB

SELECT [SWHSB], [OWNERB] As Cust, COUNT(INSERTCHARGE) AS INSERTCHARGE
FROM [TestDB].[dbo].[VEMMA_INSERT]
WHERE SWHSB = @SWHSB And OWNERB=@OWNERB AND [SHIP Date] Between @StartDate And @EndDate
GROUP BY SWHSB, [OWNERB]

From Access, I created a pass-through query to execute the stored procedure (EXEC pt_VemmaInsert). When I run it, of course, nothing comes over. Based on what I have provided, can someone guide me in the right direction of revising my pass-through query to run my stored procedure and access only the data I need based on startdate, enddate, site, and client?

Thanks so much. :banghead:

 

[TJPoorman]

Try taking a look at this thread.

Unfortunately, Access doesn't make it easy to pass parameters to a pass-through-query

 

[anissaw]

Thanks so much. I am looking at the threads and will update you on the status as well as if I need any additional assistance.

 

[anissaw]

Good afternoon -

Sorry, I had to work on other tasks and couldn't test out the suggestions until now. The thread provided didn't give me insight into how to restrict only data based on the text box values on my Access form. Originally, my Access code was accessing tables in Access. Now, I need to have this extract data from SQL Server tables only based on the values in a text box on the Access form.
How do I take the code below that is pointing to the Access tables to point to the SQL server table only based on the values of my Text boxes.

Sorry if I am too wordy.


--Access vba code;

SELECT dbo_VEMMA_INSERT.SWHSB, dbo_VEMMA_INSERT.OWNERB AS Cust, Count(dbo_VEMMA_INSERT.INSERTCHARGE) AS CountOfINSERTCHARGE
FROM dbo_VEMMA_INSERT
WHERE (((dbo_VEMMA_INSERT.SWHSB)=[Forms]![BILLOPTIONS]![Text100]) AND ((dbo_VEMMA_INSERT.[SHIP DATE]) Between ([Forms]![BILLOPTIONS]![TxtStartDate]) And ([Forms]![BILLOPTIONS]![TxtEndDate])) AND ((dbo_VEMMA_INSERT.OWNERB)=[Forms]![BILLOPTIONS]![Client]) AND ((dbo_VEMMA_INSERT.ITEMNB) In ("1001208","1001206","1001207","1001210")))
GROUP BY dbo_VEMMA_INSERT.SWHSB, dbo_VEMMA_INSERT.OWNERB;


Anissa

 

[Frothingslosh]

One possibility is having a VBA procedure assemble the SQL statement for the query at runtime (inserting the chosen values directly), update the SQL property of the querydef, and then execute it.