Slow performance on linked tables in Access 2002, in Office Access 2003, and in Office 2007

Article translations Article translations
Article ID: 275085 - View products that this article applies to.
This article was previously published under Q275085
For a Microsoft Access 2000 version of this article, see 261000.
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

SYMPTOMS

When you open a linked table in Microsoft Access 2002, in Microsoft Office Access 2003. or in Microsoft Office Access 2007 that can display related records in a subdatasheet, the process takes noticeably longer than it does if you use the same database in Microsoft Access 97.

CAUSE

This behavior occurs if there are many linked tables in a database that also contains many relationships, and the table that you are opening has its Subdatasheet Name property set to [Auto].

RESOLUTION

To work around this issue, set the table's Subdatasheet Name property to [None]. You can do this manually or by using code.

Setting the Subdatasheet Name property manually

To set the Subdatasheet Name property manually, follow these steps.

NoteThese steps only work on Access 2002.
  1. In the back-end database, open a table in Design view.
  2. On the View menu, click Properties.
  3. Set the Subdatasheet Name property to [NONE].
  4. Save and then close the table.

Setting the Subdatasheet Name property for all tables by using code

You can use a Visual Basic for Applications function to automatically set the Subdatasheet Name property for all nonsystem tables in a database to [NONE]. To do so, follow these steps:
  1. Open the back-end database.
  2. On the Database window, click Modules, and then click New.
  3. On the Tools menu, click References. Make sure the Microsoft DAO 3.6 Object Library check box is selected, and then click OK.
  4. Type or paste the following code into the new module.
    Sub TurnOffSubDataSheets()
    Dim MyDB As DAO.Database
    Dim MyProperty As DAO.Property
    Dim propName As String, propVal As String, rplpropValue As String
    Dim propType As Integer, i As Integer
    Dim intCount As Integer
    
    On Error GoTo tagError
    
    Set MyDB = CurrentDb
    propName = "SubDataSheetName"
    propType = 10
    propVal = "[None]"
    rplpropValue = "[Auto]"
    intCount = 0
    
    For i = 0 To MyDB.TableDefs.Count - 1
        If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
            If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
                 MyDB.TableDefs(i).Properties(propName).Value = propVal
                 intCount = intCount + 1
            End If
        End If
    tagFromErrorHandling:
    Next i
    
    MyDB.Close
    
    If intCount > 0 Then
        MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
    End If
    
    Exit Sub
    
    tagError:
    If Err.Number = 3270 Then
        Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
        MyProperty.Type = propType
        MyProperty.Value = propVal
        MyDB.TableDefs(i).Properties.Append MyProperty
        intCount = intCount + 1
        Resume tagFromErrorHandling
    Else
        MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets routine."
    End If
    End Sub
    
  5. In the Immediate window, type the following text, and then press ENTER to run the function:
    TurnOffSubDataSheets
    						
    Note that after a short time, if all the tables are not already updated, you receive a message box that tells you that the SubDataSheetName property for <NumberOfTablesUpdated> non-system tables has been updated to [NONE].

MORE INFORMATION

Office Access 2007, Access 2003, Access 2002, and Access 2000 allow you to view a table's related records in a subdatasheet; this functionality is not available in Access 97. To manage the relationships between the principal and related tables, the system requires additional overhead that may increase response time, particularly when a database has a large number of linked tables and a large number of relationships between tables.

The principal table in a one-to-many relationship (the table on the "one" side of the equation) can have its Subdatasheet Name property set to [None], in which case subdatasheets are not displayed. Or its Subdatasheet Name property can be set to the name of a particular related table, or to [Auto]. If the property is set to [Auto], you are able to select the related table whose records you want to see when you click the expand indicator of a record in the principal table. When you set the property to [Auto], this can reduce performance noticeably, particularly on older computers, when the database uses a large number of linked tables. This behavior does not occur when all tables are present in the same database.

Because the issue is slow performance, factors such as processor speed and available system resources may cause a database that performs adequately on one computer to perform slowly on a different computer.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Properties

Article ID: 275085 - Last Review: March 29, 2007 - Revision: 10.1
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbbug kbperformance kbnofix KB275085

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com