Pass through queries with parameters in access

[Kingz]

I was wondering, I have quite a complicated query using lag function and stuff. So I need a pass through query to successfully execute it. However, there are a couple of date parameters ( date_from, date_until) which I would like to add to the pass through query. Is that possible? Ideally, I type the dates on a form and it uses the pass through query dynamically.. Or if not, what would be work around?

 

[GPGeorge]

I was wondering, I have quite a complicated query using lag function and stuff. So I need a pass through query to successfully execute it. However, there are a couple of date parameters ( date_from, date_until) which I would like to add to the pass through query. Is that possible? Ideally, I type the dates on a form and it uses the pass through query dynamically.. Or if not, what would be work around?

Yes, as a matter of fact, one can do this in a couple of ways.

One is simply to rewrite the SQL in the passthru just prior to executing it, using VBA.

Another is to use ADO and pass the parameters to the stored proc in that procedure.

I guess it might be a coin flip which one is most comfortable to you.

Having done it both ways in different projects, I lean towards the former, but if coding is your thing and you are comfortable with ADO, that might appeal to you.

What's your preference?

 

[cheekybuddha]

Is that possible?

Yes.

How are you executing your pass-through?

 

[Minty]

I don't think you could use Lag or any of the other windowing functions available in SQL other than in a pass-through, unless I'm missing something?

 

[GPGeorge]

I don't think you could use Lag or any of the other windowing functions available in SQL other than in a pass-through, unless I'm missing something?

That's correct. Lag() and Lead() are used with System_Versioned tables (temporal tables).

Coincidentally, I'm in the process of uploading the video of Kent Gorrell's recent presentation to our Access User Group in which he discussed this topic. It should be settled and available tomorrow. I'll update the link at that time.