Pass through Queries slow 
Author Message
 Pass through Queries slow

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?


Sun, 27 Nov 2005 22:35:28 GMT  
 Pass through Queries slow
Hi Brian,

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,

--
Armen Stein
J Street Technology, Inc.


Quote:
> 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?



Mon, 28 Nov 2005 01:19:06 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Passing Data from a Form to a Pass Through Query

2. Pass-Through Query - Parameter passing question

3. Incidentally can anyone tell me how to pass a parameter though a pass-through query

4. Passing Parameters into a Pass-Through Query

5. => Passing Params into Pass-Through Queries

6. How to pass parameters to stored procedure via pass-through query

7. Passing Parameters to SQL Pass-Through Query

8. how to pass parameter to Pass-through query

9. How to pass parameters to pass-through queries??

10. PASSING PARAMETERS to a PASS Through Query


 
Powered by phpBB® Forum Software © phpBB Group