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

Setting the Subdatasheet Name property manually

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

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].