Jonathan said:
Hi, I have heard that having tables with subdatasheet on/set to auto can
serverly slow the performance of a database. Is this correct? If it is
correct, is there a simple way to turn this off/set to none for every table?
Public Sub tt_TurnOffSubDataSheets(strBEMDBPathandName As String)
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String
Dim propType As Integer, i As Integer
Dim intCount As Integer
On Error GoTo tagError
' @@@@ what if strBEMDBPathandName is empty?
Set MyDB = DAO.OpenDatabase(strBEMDBPathandName)
propName = "SubDataSheetName"
propType = 10
propVal = "[NONE]"
intCount = 0
' 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
intCount = intCount + 1
tagFromErrorHandling:
End If
End If
Next i
MyDB.Close
' If intCount > 0 Then
'
' MsgBox "The " & propName & " value for " & intCount & "
non-system tables has been updated to " & _
' propVal & "." & vbCrLf & vbCrLf & _
' "(This is an informational message only and isn't very
important.)"
' End If
Exit Sub
tagError:
If Err.Number = 3270 Then ' Property not found.
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
Exit Sub
Resume
End Sub
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -
http://msmvps.com/blogs/access/
