Change table property 'subdatasheet name' via code

[Jim M]

After learning that the 'subdatasheet name' property set to "[Auto]"
can adversely effect database speed, I wanted to send a small code
snippent to my end users to reset the SubdatasheetName Property so they
don't have to manually cycle through 35 or 40 tables and change the
property. VBA help says: "To set the SubdatasheetName property by using
Visual Basic, you must first either:
-Set the property in table Design view by pointing to Properties on the
View menu.
-Create the property by using the DAO CreateProperty method."

I tried but can't figure out how to do either. Why should I need to
create a property if it already exists? I saved myself some clicks by
running:

Public Sub rmvSubDataSheetName()
Dim db As Database
Dim tbl As TableDef
Dim tdfLoop As TableDef
Dim intContinue As Integer

Set db = CurrentDb()
With db
For Each tbl In .TableDefs
DoCmd.OpenTable tbl.Name, acViewDesign
DoCmd.SelectObject acTable, tbl.Name
DoCmd.RunCommand acCmdProperties
--------THIS IS WHERE I AM STUCK I END UP CLICKING WITH THE MOUSE
Next tbl
End With
End Sub

I want to change the property "subdatasheetname" to "[None]" but I
can't figure out how. Any suggestions?

Thanks.

[Wayne Gillespie]

On 24 Aug 2006 14:56:57 -0700, "Jim M" <ma*****@rci.rutgers.eduwrote:

>After learning that the 'subdatasheet name' property set to "[Auto]"
can adversely effect database speed, I wanted to send a small code
snippent to my end users to reset the SubdatasheetName Property so they
don't have to manually cycle through 35 or 40 tables and change the
property. VBA help says: "To set the SubdatasheetName property by using
Visual Basic, you must first either:
-Set the property in table Design view by pointing to Properties on the
View menu.
-Create the property by using the DAO CreateProperty method."

I tried but can't figure out how to do either. Why should I need to
create a property if it already exists? I saved myself some clicks by
running:

Public Sub rmvSubDataSheetName()
Dim db As Database
Dim tbl As TableDef
Dim tdfLoop As TableDef
Dim intContinue As Integer

Set db = CurrentDb()
With db
For Each tbl In .TableDefs
DoCmd.OpenTable tbl.Name, acViewDesign
DoCmd.SelectObject acTable, tbl.Name
DoCmd.RunCommand acCmdProperties
--------THIS IS WHERE I AM STUCK I END UP CLICKING WITH THE MOUSE
Next tbl
End With
End Sub

I want to change the property "subdatasheetname" to "[None]" but I
can't figure out how. Any suggestions?

Thanks.

Not sure where I got this from, but it does the trick.

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

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 & "."

End Function
Wayne Gillespie
Gosford NSW Australia