On 24 Aug 2006 14:56:57 -0700, "Jim M" <mandala@rci.rutgers.eduwrote:
Quote:
>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
