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.

    • Proposed as answer by Vaidis OK Thursday, October 01, 2015 10:55 AM
    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
  • Hi,

    The same attached table in 1 file opens in 1 second and in the problem file takes like 1 minute. I changed autocorrect, compact, delete and create link again and didn't fix it. I created a new blank database, copied all objects from the problem file to the new file and then the attachments start to open in 1 second.

    Ian

    Thursday, July 21, 2016 6:38 PM
  • Andrey

    In my experience, subdatasheets slow everything down. I run a routine on all of the databases I inherit.

    Public Function dev_TbrTurnOffSubDataSheets()
    'Purpose  : Access sets all tables' SubDataSheets to Auto which slows up loading time.
    '           This function will set the property to [none].
    'DateTime : 1/14/2004 09:20
    'Author   : Bill Mosca
        Dim db As DAO.Database
        Dim prop As DAO.Property
        Dim propName As String, propVal As String
        Dim propType As Integer, i As Integer
    
        On Error Resume Next
    
        Set db = CurrentDb
    
        propName = "SubDataSheetName"
        propType = 10
        propVal = "[NONE]"
    
    
        For i = 0 To db.TableDefs.Count - 1
    
            If (db.TableDefs(i).Attributes And dbSystemObject) = 0 Then
    
                If db.TableDefs(i).Properties(propName).Value <> propVal Then
                    db.TableDefs(i).Properties(propName).Value = propVal
                End If
    
                If Err.Number = 3270 Then
                    Err.Clear
                    Set prop = db.TableDefs(i).CreateProperty(propName)
                    prop.Type = propType
                    prop.Value = propVal
                    db.TableDefs(i).Properties.Append prop
                Else
                    If Err.Number <> 0 Then
                        Resume Next
                    End If
                End If
    
            End If
        Next i
    
        MsgBox "The " & propName & _
                " value for all non-system tables has been updated to " & propVal & "."
    
        Set prop = Nothing
        Set db = Nothing
    
    End Function
    


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Thursday, July 21, 2016 8:43 PM