Improve report speeds by about 20%, A2K

[930driver]

Access 2000 has a 'feature' in table view which allows you to see sub tables by clicking the '+' to the left of the record. In trying to diagnose slow performance when using a back end on a Win2K server, I discovered that if I turn this off my reports run about 20% faster. Below is code to turn this off for all tables. Watch out for line wraps!



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 As Integer
Dim intChangedTables As Integer

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 &quot;Error: &quot; & Err.Number & &quot; on Table &quot; _
& MyDB.TableDefs(i).Name & &quot;.&quot;
MyDB.Close
Exit Function
End If
End If

End If
Next i

MsgBox &quot;The &quot; & propName & _
&quot; value for all non-system tables has been updated to &quot; & propVal & &quot;.&quot;

MyDB.Close

End Function
&quot;The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!&quot;

 

[JimHorton]

Yes, that + 'feature' is another one of MS's 'because we can' feature--if some MS developer comes up with a 'clever' idea, well, by God, then their users *must* want it!
--Jim

 

[montrose]

Thanks for posting this. Don't see how MS ever thought this was a good 'feature'?!