Turning off subdatasheet view for SQL Linked Tables in Access

[djblois]

I know it is recommended to turn off subdatasheet view on tables to speed up access to tables. however, SQL linked tables also have that feature on but do not allow you to adjust them. Is there any way to turn it off for these tables?

Microsoft Access Tip: Set Table Subdatasheet Name Property to [None], or explicitly specify the name. (fmsinc.com)

[Minty]

I think from memory that you change this in the table design window even though it says you can't change/save the settings?

[Gicu]

Try to run this function and see what happens:

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


response = MsgBox("Do you wish to optimize all non-system tables by setting the Sub DataSheetName property to [None]?", vbYesNo + vbQuestion, "Optimize all non-system tables")
If response = vbNo Then Exit Function


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


End Function

Cheers,