Hello there
I have huge database on access 2003 with 400 tables on it (most of them are
linked to sql server)
When i convert it from access 97 the subdatasheet name property become Auto
To improve performance i need to set the subdatasheet name to none.
Is there a way to do this on code?
Yes. I apologize to whoever I got this code from - you didn't identify
yourself in it and I've forgotten which website it was on, but it
works VERY well and I've used it routinely.
Public Function TurnOffSubDataSh() As Integer
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Const conPropName = "SubdatasheetName"
Const conPropValue = "[None]"
TurnOffSubDataSh = 0
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
If tdf.Connect = vbNullString And Asc(tdf.Name) <> 126 _
Then 'Not attached, or temp.
If Not HasProperty(tdf, conPropName) Then
Set prp = _
tdf.CreateProperty(conPropName, dbText, conPropValue)
tdf.Properties.Append prp
TurnOffSubDataSh = TurnOffSubDataSh + 1
Else
If tdf.Properties(conPropName) <> conPropValue Then
tdf.Properties(conPropName) = conPropValue
TurnOffSubDataSh = TurnOffSubDataSh + 1
End If
End If
End If
End If
Next
Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
End Function
John W. Vinson[MVP]
