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?

 

[Pat Hartman]

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.

How do you know that? Did you try using an Access querydef? They take parameters.

If you tested the querydef and you think it is too slow, convert the SQL to T-SQL and embed it in your VBA code. That way you can concatenate your parameters before you send the query using DAO or ADO.

Create a variable to hold the Select and From clauses. Then a second variable for the WHERE. Then concatenate the two when you send the query.

strWhere = " Where somedt >= #" & Format(date_from, 'yyyy/mm/dd') & "# AND somedt <= #" & Format(date_Until, 'yyyy/mm/dd') & "#"

I formatted the date as the unambiguous ymd format in case you are in the UK or some other place where you use a date format other than mdy as your default.

 

[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.