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?
dblankmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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
0
dblankmanAuthor Commented:
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?
0
Dale FyeCommented:
You basically have to create the pass-thru syntax via code.  You cannot actually define a parameter like you can in Access.  That is why I like to use SQL Server stored procedures, it is much easier to change the syntax of a pass-thru query which calls a stored procedure.  This checks to see whether the text field [Something] contains a value, if not, it passess NULL to the stored procedure as the parameter, otherwise, it passes the value of the field [Something].

Private Sub txt_Something_Afterupdate

    currentdb.querydefs("passThruReturnsRecords").SQL = "exec YourStoredProcedureName " _
           & iif(me.txt_Something & "" = "", "Null", me.txt_Something)

End Sub

You would then need to call this pass-thru query some how, maybe use it as the source for a subform, or list/combo control.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.