Print Page | Close Window

Set SubDatasheet to None in all tables

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Long, Crystal
Forum Description: Access Basics is designed for those of you with a thirst to understand the fundamentals of Access with programming in mind ... whether you realize it or not.
Printed Date: 18 Nov 2018 at 1:56pm

Topic: Set SubDatasheet to None in all tables
Posted By: Crystal Long
Subject: Set SubDatasheet to None in all tables
Date Posted: 25 Jul 2013 at 11:36am
Set SubDatasheet to None in all tables

Speaking about subdatasheets, Allen Browne said, "Clearly, this is not a good idea and may have unintended consequences in applications imported from earlier versions. Worse still, there are serious performance issues associated with loading a form that has several subforms where Access is figuring out and collecting data from multiple more related tables."" rel="nofollow -

I have modified Allen's code that turns off all subdatasheets to provide a message box with counts at the end, and to allow an error instead of checking if there is a subdatasheet property (using more) before changing it.

Public Sub SetSubDatasheetNone()
   'crystal 10-27-06, 130725
   ' download this code from:
   'based on code written by Allen Browne
   '  click HERE
   '     press F5 to Run!
   '     (or choose 'Run, Run Sub/Userform' from the menu)
   'set the Subdatasheet property to [None]
   'in all user tables
      'NEEDS reference to Microsoft DAO Library
      'Microsoft Office ##.0 Access Database Engine Object Library
   Dim tdf As DAO.TableDef _
      , prop As DAO.Property
   Dim nCountDone As Integer _
      , nCountChecked As Integer _
      , mBoo As Boolean _
      , sStr As String
   'cheap but it works <g>
   On Error Resume Next
   nCountDone = 0
   nCountChecked = 0
   For Each tdf In CurrentDb.TableDefs
      'skip Microsoft System tables
      If Left(tdf.Name, 4) <> "Msys" Then

         mBoo = False
         nCountChecked = nCountChecked + 1
         Err.Number = 0
         sStr = tdf.Properties("SubdatasheetName")
         If Err.Number > 0 Then

            Set prop = tdf.CreateProperty( _
               "SubdatasheetName", dbText, "[None]")

            tdf.Properties.Append prop
            mBoo = True
            'thanks, Allen!
            If tdf.Properties("SubdatasheetName") <> "[None]" Then
               tdf.Properties("SubdatasheetName") = "[None]"
               mBoo = True
            End If
         End If
         If mBoo = True Then
            nCountDone = nCountDone + 1
         End If
      End If
   Next tdf
   Set prop = Nothing
   Set tdf = Nothing
   MsgBox nCountChecked & " tables checked" & vbCrLf & vbCrLf _
      & "Reset SubdatasheetName property to [None] in " _
      & nCountDone & " tables" _
      , , "Reset Subdatasheet to None"
End Sub

Warm Regards,

Microsoft MVP
Classroom and Remote Training and Development
... Connect to me, lets build it together!

free book, tips, and tools:" rel="nofollow -

(: have an awesome day :)

Warm Regards,
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together
~have an awesome day ~

Print Page | Close Window