none
Opening Tables slow

    Question

  • Hi,

    For some reason, when I try to open any table in one of my Access databases there is a long pause (30 seconds or thereabouts) before the data is displayed.

    Queries and forms don't suffer from this issue, just tables. And the same occurs regardless of the size and complexity of the table. Viewing the design mode of a table doesn't have any issues either.

    Access 2003, WinXP. DB has been compacted.

    Does anyone have any suggestions why this might be occurring? And more importantly what I can do to fix it?

    HD.

    Tuesday, August 10, 2010 1:20 AM

Answers

  • Thanks Graeme,

    I already had 'Name AutoCorrect' turned off, instead it turned out that the 'Subdatasheet Name property' should have been set to '[NONE]'. I got that tip from Tony's website.

    "Slow performance on linked tables in Access 2002, in Office Access 2003, and in Office 2007"
    http://support.microsoft.com/kb/275085/

    I will certainly review all of Tony's other suggestions.

    HD.

    Tuesday, August 10, 2010 4:49 AM

All replies

  • Hi HD,
     
    Do you have primary keys on all your tables?
     

    Hi,

    For some reason, when I try to open any table in one of my Access databases there is a long pause (30 seconds or thereabouts) before the data is displayed.

    Queries and forms don't suffer from this issue, just tables. And the same occurs regardless of the size and complexity of the table. Viewing the design mode of a table doesn't have any issues either.

    Access 2003, WinXP. DB has been compacted.

    Does anyone have any suggestions why this might be occurring? And more importantly what I can do to fix it?

    HD.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Tuesday, August 10, 2010 1:24 AM
  • Yes, and it doesn't seem to make any difference if I remove them.

    HD.

     

    Hi HD,
     
    Do you have primary keys on all your tables?
     

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

    Tuesday, August 10, 2010 1:38 AM
  • If you open one table, then open a second table. What's the performance like when you open the second table?
     

    Yes, and it doesn't seem to make any difference if I remove them.

    HD.

     

    Hi HD,
     
    Do you have primary keys on all your tables?
     

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Tuesday, August 10, 2010 2:02 AM
  • The second (and subsequent) table is slow as well.

    Also, if I go straight to design mode (quick) it is slow to switch to datasheet view. The first time datasheet view is opened it is slow regardless, if I then switch to design mode it is quick to switch back and forth between the two.

    Although I said they weren't, some SELECT queries are slow to open in design mode as well. It seems to depend on if they are based on a separate UNION query. Shouldn't effect the tables though. All of the INSERT INTO action queries and UPDATE action queries are slow to open in design mode. DELETE action queries open fine.

    Thanks for your help with this.

    HD.

     

    If you open one table, then open a second table. What's the performance like when you open the second table?
     

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Tuesday, August 10, 2010 2:29 AM
  • Well, I've fixed it.

    I had three linked tables in the database. Removing the linked tables meant all the other tables started functioning normally again.

    This isn't really a solution because I will still need to figure out a way to get the external data into my DB but at least I don't need to pull my hair out waiting!

    Do you know why a linked table would be effecting the rest of the DB?

    HD.

    Tuesday, August 10, 2010 2:46 AM
  • I have a feeling it's related to NameAutoCiorrect, but I recommend you have a read through Tony Toews' page on performance and take ALL the actions he recommends.
     
     

    Well, I've fixed it.

    I had three linked tables in the database. Removing the linked tables meant all the other tables started functioning normally again.

    This isn't really a solution because I will still need to figure out a way to get the external data into my DB but at least I don't need to pull my hair out waiting!

    Do you know why a linked table would be effecting the rest of the DB?

    HD.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Tuesday, August 10, 2010 3:27 AM
  • Thanks Graeme,

    I already had 'Name AutoCorrect' turned off, instead it turned out that the 'Subdatasheet Name property' should have been set to '[NONE]'. I got that tip from Tony's website.

    "Slow performance on linked tables in Access 2002, in Office Access 2003, and in Office 2007"
    http://support.microsoft.com/kb/275085/

    I will certainly review all of Tony's other suggestions.

    HD.

    Tuesday, August 10, 2010 4:49 AM
  • Hi Graham,

    one question about 'Subdatasheet Name property'. Does it affect all the work with these tables (SELECT, INSERT queries) or only opening them in datasheet view?

    Tuesday, August 10, 2010 6:40 AM
  • Hi Andrey,
     
    MVP Allen Browne has an excellent discussion of the problems associated with Name AutoCorrect here: http://allenbrowne.com/bug-03 .html
     

    Hi Graham,

    one question about 'Subdatasheet Name property'. Does it affect all the work with these tables (SELECT, INSERT queries) or only opening them in datasheet view?


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Tuesday, August 10, 2010 2:34 PM