Six months ago we converted an Access 2003 application to use MSSQL 2005 as the database, and are gradually converting reports to use pass-thru queries, with either T-SQL or stored procedures, to take advantage of the speed & power of the SQL Server.
There are many complex reports, some of which have embedded summary subreports, and therein lies the snag - Access does not allow you to use a stored procedure for subreports.
The main reports that have user criteria are easy - create a stored procedure that accepts parameter(s) and the pass-thru query is re-written in the button click event. So the VBA code for that might look like this:
CurrentDB.QueryDefs("MyQuery").SQL =
"EXEC dbo.prcMyQuery '" & Forms!MyForm!MyDate & "' "
and the query executes this:
EXEC dbo.prcMyProcedure '07/19/2009'
thus returning the desired records.
But what to do about the subreport? That query is often far more complex than the main query, since it provides a summary with aggregates. . .
The solution is to create a View, link to the view (which Access treats as an attached table) and then create a simple Access query to apply the filter criteria. So if the view is attached as
viwMyView
then the Access query might look like this:
SELECT viwMyView.*
WHERE viwMyView.DateField = Forms!MyForm!MyDate
which then applies the same filter to the subreport, that is applied to the main report. We still get the power of SQL Server to build up the summary data, and the number of rows is small enough that the Access query overlay does not impact the performance.
