Try to run this function and see what happens:
Code:
Function TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String
Dim propType As Integer
Dim propVal As String
Dim strS As String
Dim i, intChangedTables
Dim response As Integer
response = MsgBox("Do you wish to optimize all non-system tables by setting the Sub DataSheetName property to [None]?", vbYesNo + vbQuestion, "Optimize all non-system tables")
If response = vbNo Then Exit Function
Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[NONE]"
On Error Resume Next
For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
If MyDB.TableDefs(i).Properties(propName).Value <> propVal Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intChangedTables = intChangedTables + 1
End If
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
Else
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Number & " on Table " & MyDB.TableDefs(i).Name & "."
MyDB.Close
Exit Function
End If
End If
End If
Next i
MsgBox "The " & propName & " value for all non-system tables has been updated to " & propVal & "."
MyDB.Close
End Function
Cheers,