Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    hobart, tasmania, australia
    Posts
    21
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have an access report that is generated from 2 queries accessing a data table that gets up to a couple of million records in size. Running the report takes some time - probably around 15 minutes due to the size of the database and my slow computer. I can cope with this however any change to the report design leaves me waiting an extreme amount of time to proceed with editing. I'm guessing by the amount of disc activity taking place that the report design is actually running the queries in the background. Is there a way of turning off the querys while in report design mode?

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Is your backend database Access or SQL?

    If it's SQL then using a pass through query will reduce the time to run by around 95%

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by mike bazan View Post
    I have an access report that is generated from 2 queries accessing a data table that gets up to a couple of million records in size. Running the report takes some time - probably around 15 minutes due to the size of the database and my slow computer. I can cope with this however any change to the report design leaves me waiting an extreme amount of time to proceed with editing. I'm guessing by the amount of disc activity taking place that the report design is actually running the queries in the background. Is there a way of turning off the querys while in report design mode?
    Use a named query as the recordsource for the report. Then prior to opening the report in design mode, change the SQL in the query to something that will only return no records yet still return the same fields. This could be as simple as putting in a selection criteria on an indexed field, something like "... WHERE CustID=0...". Of course, you will need to remember to change it back.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    hobart, tasmania, australia
    Posts
    21
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes I could change the query to return no data. It seems a strange requirement however.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by mike bazan View Post
    Yes I could change the query to return no data. It seems a strange requirement however.
    Actually, I think the only time I had a similar problem was when the recordsource included a crosstab query. It wasn't the # of records returned that caused the report to opening slowly in design mode, I think it was because Access had to run the crosstab query do it could figure out the fields (just a guess).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    That's a good possibility, Mark. I have a similar design where the data source for a report is a query which joins several tables to a crosstab query. And it does take a long time to open that in design view.
    Wendell

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    hobart, tasmania, australia
    Posts
    21
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes as it happens it is a report populated from a crosstab query using data from another query!

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    One solution you might consider is using fixed columns names - do a Bing or Google search (the Lounge one isn't available at the moment) and you should be able to find a number of pages that tell you how to do that.
    Wendell

  9. #9
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've run into this sort of situation a few times. There are almost always ways to dramatically improve speeds, but you might have to be willing to experiment a bit.

    First, check that your table is indexed properly for the selection criteria. I suspect that is probably already the case, but it is worth mentioning.
    Second, experiment with some make tables. In the past, I have made a 2nd table based on the first queries results, then run crosstabs on that. If you make the temporary table, then you might be able to put in indexes to speed the crosstab up. However, the index building takes its own time too. Just try one thing, then another. If your backend is a linked table, then have the temporary table local (ie in the front-end).

    If that should prove to be lots faster, then you can implement the sequence in code: 1) Clear the temporary table, 2) run query to populate the table. This code could be in the report open event.

    With regard to being slow during design time, why not use a make table to hold the results of the crosstab? Then just use that table during design time as your record source, but switch to the crosstab for production.

    Just a few thoughts. Hopefully one of them will help.

Posting Permissions

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