Update pass through query of report recordsource

[Microsoft Access]

I am working on upsizing my access backend database to SQL Server. I have
linked all sql tables to the frontend and am now running into the issue of
slow performance on one of the reports.

One way to improve that I thought was to change the recordsource query
(which is a saved query) to a pass through query. However there are form
controls that drive the parameters. So I was wondering if a better way to
do this is to create the sql query string in vba and then set the report
record source. Is this a viable option? Is there a better way?

Please ask any leading questions if this is too vague.

Access 2007
SQL Server Express 2005

 

[Duane Hookom]

You are close. I would create the SQL statement in code and use DAO to update
the SQL property of your p-t query:

CurrentDb.QueryDefs("qsptYourQueryName").SQL = strSQL

Then open your report and watch it fly.

 

[Microsoft Access]

Great, thanks

You are close. I would create the SQL statement in code and use DAO to
update
the SQL property of your p-t query:

CurrentDb.QueryDefs("qsptYourQueryName").SQL = strSQL

Then open your report and watch it fly.