Background:
Access FE with SQL Server Express BE. The main table has 1.5 million records, so report time is slow. I want to limit the records going across the LAN.
Baby steps... just working on Reports right now.
Still trying to convert the record sources (queries) from Access saved queries to something in SQL Server Express.
As I understand it, Views are akin to Access saved queries, except Views cannot have parameters. So Views are out.
I have one Access query converted to a Stored Procedure and I just created an Inline Table Valued Function.
There are two parameters - company PK (long) and effective date (Date).
Both the SP and the Fn appear to return the same data (still checking the values).
Q1) Is one better than the other as far as being the record source for a report?
Q2) I can execute both is SSMS, but how do I call them from the Access report? (can't find any examples)
Q3) Do I create a saved pass through query, edit the query def to change the parameters (VBA) and use the saved PT query as the record source in the Access report? OR ..........????
TIA