Search code examples
sqlstringms-accesspass-through

Dealing with filtered Pass Through Query in MS Access

I have a relatively complex SQL query (complex to run in Access) and want to run it in MS Access. It works with the pass-through query well but going forward I will face an issue that is related to a filter I apply in the query. I select the current report date within the where function. Below is a part of my query I try to handle ;

select  LS.PID_FACILITY, LS.ASOF_DTE, LS.DATA_CYCLE_FLG,  LS.CUST_ACC, LS.CUST_SMUN, LS.CUST_NME, LS.CUST_CTY, 

WHERE LS.ASOF_DTE='19-SEP-22'

I do not want to change asof_dte filter manually everyday. If this was a normal access query I could join another table that includes only the current report date. But I cannot do it in a pass-through query. What is the alternative way to do it? I read something about creating variables or strings, but I could not relate them to my problem, since I am a beginner at creating such solutions.
Thank you all.

Solution

  • Well, there's two VERY interesting things here.

    First, YES a great idea to include the date in the PT query. But, you don't want to change that date each time.

    Solution: Add a parameter to the query, and then from Access code add that parameter. It is VERY easy to do this (one line of code!!! - don't adopt the zillion examples out there that has a boatload of ADO code - NOT required!).

    However, BEFORE we start dealing with above?

    A MUCH better and simple, less work way to approach this?

    In place of stored procedure?

    If possible, create a view, and use that for the report.

    Why? Because you then get TWO VERY valuable bonuses.

    First, you can freely use the reports "where" clause, and it respects the where clause and STILL runs server side!!!

    In other words, create a view for that existing query, but WITHOUT the date set in that view.

    You then link to the view from access client side.

    Now, to open (filter) the report, you can do this:

    docmd.OpenReport "MyReport",acViewPreview,,"LS.ASOF_DTE='19-SEP-22'"

    Now, of course the above "where" clause can be a variable (string).

    NOTE SUPER but SUPER careful here:

    If you base the report on a pass-though query (that then uses the stored procedure), then the filter occurs CLIENT SIDE!!!! (All rows will be returned and THEN filtered if you report is based on that stored procedure).

    But, if you use a view? The the filter makes it to the server side!!!!

    While both the pass-through query or the "view" can be filtered with the above "open report" and the where clause we have above?

    The view will still filter server side - the pass-though query will NOT!!!

    Now, the third way, is of course to build the stored procedure to accept a date parameter.

    You then could do this:

    with Currentdb.QueryDefs("MyPassThoughQueryGoesHere")
    .SQL = "EXEC MyStoreProc " + "19-SEP-22"
END WITH

docmd.OpenReport "MyReport",acViewPreview

    So, you CAN add and have a PT query and add a parameter as per above.

    However, unless that stored procedure has some special code, you are MUCH better off to create a view server side, base the report on that view, and simple pass + use the traditional "where" clause of the open report command. Even if that view has no filter, returns all rows in the table?

    With the "where" clause of the open report command, ONLY those rows meeting that criteria will be pulled down the network pipe.

    So, say a invoice table with 1 million rows.

    Create a view, link the view in Access.

    Base report on that view.

    Now, do this:

    docmd.OpenReport "rptInvoice",,,"InvoiceNum = 134343"

    The above will ONLY PULL down 1 row from the server. Even if the view has no filter and would return 1 million rows.

    So, using a view is less work then creating the stored procedure.

    But, you can modify the stored procedure to accept a parameter, and then as noted use the above example to modify the PT query you have, and THEN open the report.

    I think overall, it is less work to use view. Furthermore, if you have a slow running report now?

    Replace the query (move it) to SQL Server side. Get it working. Now link to that view (give it same name as what the client side query was in Access).

    Now, EVEN if you had some fancy filter code in VBA, and used openReport with the "where" clause? It will now work, only pull the records down the network pipe, you get stored procedure performance without the hassles. And the date format and "where" clause for open report is Access/VBA style - not SQL Server style SQL.

    So, high recommend you try and dump the stored procedure and use a view (and EVEN better is any where clause works - not just one based on pre-defined parameters for the stored procedure - so you not limited to parameters).

    However, no big deal - the above "EXEC dbo.MyStoreProce " & strDate example would also work fine if you have a date parameter you wish to supply to the pass-though query.