asked on

How to create a pass through parameterized query using ms access 2010

I have an application that was originally developed with an Access front and back end. I am converting it to an access front end with sql server back end.

There is one query which takes a long time even as a passthrough query. The query is:
SELECT Feeding.EventTurtleID ,
Feeding.FeedingDate ,
Feeding.FeedingFish ,
CASE WHEN Leftover.FeedingFishLeftover IS NULL THEN 0
ELSE Leftover.FeedingFishLeftover
END AS FeedingFishLeftover ,
CASE WHEN Feeding.FeedingFish = 0 THEN NULL
ELSE Feeding.FeedingFish - Leftover.FeedingFishLeftover
/ Feeding.FeedingFish
END AS [Fish%] ,
Feeding.FeedingLettuce ,
CASE WHEN Leftover.FeedingLettuceLeftover IS NULL THEN 0
ELSE Leftover.FeedingLettuceLeftover
END AS FeedingLettuceLeftover ,
CASE WHEN Feeding.FeedingLettuce = 0 THEN NULL
ELSE ( Feeding.FeedingLettuce - Leftover.FeedingLettuceLeftover )
/ Feeding.FeedingLettuce
END AS [Lettuce%]
FROM dbo.FeedingLeftover AS Leftover
INNER JOIN dbo.FeedingLeftover AS Feeding ON Leftover.EventTurtleID = Feeding.EventTurtleID
AND Leftover.FeedingDate
- 1 = Feeding.FeedingDate;

However if I add " WHERE EventTurtleID = 3110" the query is executed rapidly.

In a form, I have a control which allows me to get the EventTurtleID parameter. Is there a way to populate a subform with the results of the parameterized query where the results are obtained from a pass through query?

Dale Fye

Have you tried moving that code to a stored procedure in SQL Server and running it there? If not, give that a try.

Then you would call that from an Access pass-thru query with "returns records" set to yes with a syntax like:

exec YourStoredProcedureName

or if you want to pass it that EventTurtleID like below. You would obviously need to change the syntax of the query to include a WHERE clause for the EventTurtleID, something like:

AND ((@EventTurtleID IS NULL) OR ([EventTurtleID] = @EventTurtleID))

exec YourStoredProcedureName 131
or
exec YourStoredProcedureName NULL

dblankman

ASKER

What is the Access syntax for passing the parameter. I can use an after Update event to determine the parameter value, but how do I insert that value into a passthrough query?

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial