Pass Through Query Report

[LadyDi]

I have created a pass through query to show the order volume for a specific group of sales reps. I would like to get a report set up based on that query that will summarize the data (i.e. tell the number of orders entered and the dollar amount for those orders instead of listing all order individually). I tried creating a report, but everytime I try to run it, my database locks up on me. Is there a way to generate a report based on a pass through query? Any assistance you can provide, would be greatly appreciated.

 

[pbaldy]

I saw your post on this yesterday, didn't answer as I hoped someone who had an better answer would post. I have several reports based on pass-through queries (to SQL Server). The only downside I've had is that sometimes there's a lag working in design view. My assumption is that it keeps running the query to validate field names or something. How long does your query take to run by itself?

 

[LadyDi]

It just takes a couple of minutes to run by itself.

 

[pbaldy]

Are you sure the database is locking up and not just waiting for the query? If you have DSum's or something in the report looking at the query, that could multiply the time it takes the report to run.

 

[LPurvis]

Yes a report in design view has no option but to execute the passthrough to obtain the definition - as, unlike liinked tables, there's no way that the schema can be cached (it can be an ad hoc query which could even return different columns each time it runs!)

Perhaps surprisingly though, determining the schema can see Access execute the query several times!
Compare this to reading from a linked table - which has its definition cached locally - and so the server isn't even queried once!
(It's why you have to refresh linked tables when you make a server change to the schema - a small price to pay.)

Common development choices include using another source - such as a linked table pointing to a view, or even a local table which is an empty copy of your PT's results used simply for its convenience of definition. Switching to the passthrough at deployment time (or even have the report set its source as it opens... just watch for that property persisting in design view by accident! ;-)

Cheers.