Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MS Access running at a Stop !

Options
  • 12-12-2014 3:08pm
    #1
    Storm99
    Registered Users Posts: 81 ✭✭


    Hi I have built a database in Access. I have a couple of Crosstab queries that I then run reports off.

    Since I built the reports, when in Report Design Mode the whole database seems to be executing something in the back ground and it comes to a grinding halt, so that even selecting a field in the report designer takes 20 mins for the selection. So building a report that should take 5 mins is taking over 3 hours.

    I converted all my linked table to local, I have the database on my desktop so it's not over a network.

    It's similar to the issue I have come accros in Excel when you have a huge file with a lot of formula's and the Autocalculate is on, so when you make a change it takes ages.
    I don't know if there is a way to stop access from processing in the background when in design modes, to be honest I thought no execution of Queries happened until you ran the query ?

    HAs anyone else come accross this issue and found a resolution ?

    Thanks in advance !
    Tagged:


Comments

  • Options
    #2
    srsly78
    Registered Users Posts: 7,157 ✭✭✭srsly78


    Set up primary keys and indexes properly.


  • Options
    #3
    Storm99
    Registered Users Posts: 81 ✭✭Storm99


    They are set up :)


  • Options
    #4
    srsly78
    Registered Users Posts: 7,157 ✭✭✭srsly78


    Adjust them so. This is what massively affects performance, can turn a 2 day query into a 5 minute query.


  • Options
    #5
    Storm99
    Registered Users Posts: 81 ✭✭Storm99


    Even when in Design mode ? I thought Queries don't execute until you run them ?
    Also, there is only one table.


  • Options
    #6
    srsly78
    Registered Users Posts: 7,157 ✭✭✭srsly78


    Hmm that sounds odd. Databound controls etc might be doing stuff in the background. Try creating a new blank report and see if that performs ok. Then add stuff in gradually until you see your problem appear. This should help narrow down what is causing it.


  • Advertisement
  • Options
    #7
    Storm99
    Registered Users Posts: 81 ✭✭Storm99


    Will Do ! Thanks very much !


  • Options
    #8
    jmcc
    Registered Users Posts: 7,323 ✭✭✭jmcc


    srsly78 wrote: »
    Hmm that sounds odd. Databound controls etc might be doing stuff in the background. Try creating a new blank report and see if that performs ok. Then add stuff in gradually until you see your problem appear. This should help narrow down what is causing it.
    Not an Access afficionado but could the problem be with multiple joins and conflicting (or poor choice) indexes? Also if there's a lot of summarising and totalling, it might be better to build temporary summary tables and use these rather than executing multiple summarisation/totalling queries.

    Regards...jmcc


Advertisement