Pass through queries in Access 2010 Reports

Topic

New Reply

Hi there,

I’m experiencing problems when I try creating reports directly using a pass through query.

The pass through query, which runs against SQLSever 2008R2, takes an instant to return data. When I try and create a report I constantly get the hour glass coming up each time I drag a field onto the report or even just moving a field around. It seems as if there is a constant refresh(?) going on every time you try and do something with the source fields. This is so bad it’s almost impossible to build the report. Further, the report takes noticeably longer to run than the code by itself.

Am I doing this the right way or is the general practise to feed the data into a table first and then report off that?

Any suggestions and insights as to how this works, I would be most grateful.

Cheers

Niven

Reply | Quote

Replies

I’ve noticed a similar problem when the recordsource is a complicated Access query (like a crosstab or union query), but not with a passthrough. What you might do is temporarily clear the recordsource property of the report while you work on it, then re-instate it before saving.

Reply | Quote

For what it is worth, I almost never use a pass-through query as a data source for a report when using a SQL Server backend. Instead we tend to use SQL Views as the data source and the filter to get the subset of data we want. Many developers take the view that all the work should be done in SQL Server, but we’ve found that if the SQL Server tables are properly structured and indexed, you can get by just doing a query as the data source and response is just fine. On the 5 or 10% where we hit an issue, then we create views and link to them so that Access sees them as a table. With that approach, we get good results even if we are working with multi-million row datasets.

Reply | Quote

Many thanks for your responses Mark and WendellB

I tried clearing the recordsource property, but lost all the fields in the process. Don’t know if I did something wrong.

Where views are concerned, I would dearly love to create these along with stored procs, but don’t have the permissions to do so. I’ve asked for my own read write section on the target DB away from the core data, but all to no avail.

What I have done so far is read the pass through query data into an access table and then queried that. I’ve also found by keeping all the fields the same, I can create the report and then switch the queried table back to the pass through query. So far works a treat! Only problem is if you need to add or delete a field or reorganise the layout, which means you have to go through the process of reinstating the table.

Cheers,

Niven

Reply | Quote