Pass through Queries slow
We've been annoyed by this problem too. The problem is that Access runs the
passthrough query just to get the columns of the recordset. This is a
problem in design, and also when the report is run. Even before the On Open
event (which is supposed to fire before the recordset is built), Access
actually seems to run the passthrough query.
One way to speed it up is to add "WHERE 1=0" to the passthrough query in the
Close event of the report. The report will open (normally or in design)
using this empty recordset. Then, remove it again (or replace it with your
desired Where clause) in the On Open event of your report.
This trick returns the recordset very quickly, as no records could possibly
match. It is like a "Where False" in Access (though that syntax doesn't
work in SQL). But the report in Design view just needs to know the columns
from SQL, not any actual records.
We actually use this technique in production applications to get the reports
to open faster. We always prompt for selection criteria in the On Open
event, so we replace the Where clause anyway.
Hope this helps,
J Street Technology, Inc.
> I'm creating a report based on a SQL pass through query to
> SQL Server. Anytime I design the form or make a change, it
> runs the pass through query over and over again and is
> painfully slow. Can I change this behavior somehow?