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: 28 Mar 2024 at 8:19pm Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com
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 ~
|
|