Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pass through queries in Access 2010 Reports

    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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •