Common wisdom tells the serious Access developer that using subdatasheets will slow his application. In particular, the automatic subdatasheets cause the biggest slowdown. Here I’ve written some code that you can drop into any Access database and become the hero that knows how to make Access sing. My code will remove subdatasheets from all of the local user tables. Note that linked tables of any kind (Jet, Excel, text, etc.) are ignored as subdatasheets do not apply to them.

Happy computing!

Public Function NoSubDatasheet() As Boolean
   'Remove all subdatasheets from all local Jet tables. This will
   'increase overall database speed.
   'Jim Pilcher, DataOne, Inc., 720-301-0479
   'October 27, 2009
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim prp As DAO.Property
   Dim strPropName As String
   Dim strVarPropValue As String
   
   On Error GoTo ErrorHandler
   
   Set db = CurrentDb()
   
   For Each tdf In db.TableDefs
      If tdf.Connect = vbNullString Then  'Use only local Jet tables
         If (tdf.Attributes And dbSystemObject) = 0 Then
            ‘Look at only non-system tables
            strPropName = "SubdatasheetName"
            strVarPropValue = "[None]"
            tdf.Properties(strPropName).Value = strVarPropValue
   
            tdf.Properties.Delete "LinkChildFields"
            tdf.Properties.Delete "LinkMasterFields"
   
         End If
      End If
   Next tdf
   
ExitProc:
   Set prp = Nothing
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
ErrorHandler:
   Select Case Err.Number
      Case 3270
         'Property not found. Go ahead and create it.
         Set prp = tdf.CreateProperty(strPropName, dbText, strVarPropValue)
         tdf.Properties.Append prp
         Resume Next
      Case 3265
         'Item not found in this collection when trying to delete
         'a property. No problem. We don't want it there anyway.
         Resume Next
      Case Else
         MsgBox Err.Description, vbOKOnly + vbCritical, "Error " & Err.Number
         Resume ExitProc
   End Select
End Function