Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Long, Crystal
  New Posts New Posts RSS Feed - Set SubDatasheet to None in all tables
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)

Set SubDatasheet to None in all tables

 Post Reply Post Reply
Crystal Long View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 21 May 2012
Location: Colorado
Status: Offline
Points: 35
Post Options Post Options   Thanks (0) Thanks(0)   Quote Crystal Long Quote  Post ReplyReply Direct Link To This Post Topic: Set SubDatasheet to None in all tables
    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."

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:

(: have an awesome day :)

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

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.