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

>
Want a good read? Try The Summer of His Life (available on Amazon!)

Set SubDatasheet to None in all tables

 Post Reply Post Reply
Author
Message Reverse Sort Order
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."
http://allenbrowne.com/bug-09.html

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
   'strive4peace
   ' download this code from:
   '  http://www.rogersaccesslibrary.com/forum/set-subdatasheet-to-none-in-all-tables_topic614.html
   'based on code written by Allen Browne
   '      http://allenbrowne.com/bug-09.html
   '
   '  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
      'or
      '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
         Else
            '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,
Crystal

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

free book, tips, and tools:
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*


Warm Regards,
Crystal
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together

http://www.AccessMVP.com/strive4peace
http://YouTube.com/LearnAccessByCrystal
~have an awesome day ~
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

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.