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.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=614
Printed Date: 11 Dec 2017 at 2:11pm


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."
http://allenbrowne.com/bug-09.html" rel="nofollow - 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" rel="nofollow - 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 ~



Print Page | Close Window