|
|
VBA > Table > SetSubDatasheetNone
Set SubDatasheet to [None] in all tables where needed to enhance performance.
|
|
|
Set SubDatasheet to [None] in all tables where needed to enhance performance.
An advantage of turning off SetSubDatasheetNone is better performance. If tables used in forms have subdatasheets, then when forms are loaded, even when though those subdatasheets aren't necessary, all that related information is still loaded. When a form has several subforms, and each table is loading other tables, which, in turn, load other tables, this takes more time and can also cause other issues. Why sacrifice performance and risk errors? Better to turn them all off.
This table has a subdatsheet to another table on SetID. Performance would be better without it.
'*************** Code Start ***************************************************** ' download: ' http://msaccessgurus.com/VBA/Code/tablel_SetSubDatasheetNone.htm '------------------------------------------------------------------------------- ' Purpose : Set subdatasheet to [None] in all tables where needed ' Author : crystal (strive4peace), based on code by Allen Browne ' License : below code ' Code List: www.msaccessgurus.com/code.htm '------------------------------------------------------------------------------- ' SetSubDatasheetNone '------------------------------------------------------------------------------- Public Sub SetSubDatasheetNone() 'crystal (strive4peace) 061027, 160905, 200423 'strive4peace 'based on code written by Allen Browne ' Microsoft Access Flaws - Problem properties by Allen Browne ' http://allenbrowne.com/bug-09.html 'set the Subdatasheet property to [None] 'in all user tables 'needs reference to 'Microsoft DAO Library Dim tdf As DAO.TableDef _ ,prop As DAO.Property Dim iCountDone As Integer _ ,iCountChecked As Integer _ ,bChanged As Boolean _ ,sName As String 'skip errors On Error Resume Next iCountDone = 0 iCountChecked = 0 For Each tdf In CurrentDb.TableDefs 'skip Microsoft System tables If Left(tdf.Name,4) <> "Msys" Then bChanged = False iCountChecked = iCountChecked + 1 Err.Number = 0 sName = tdf.Properties( "SubdatasheetName") If Err.Number > 0 Then Set prop = tdf.CreateProperty( _ "SubdatasheetName",dbText, "[None]") tdf.Properties.Append prop bChanged = True Else 'thanks, Allen! If tdf.Properties( "SubdatasheetName") <> "[None]" Then tdf.Properties( "SubdatasheetName") = "[None]" bChanged = True End If End If If bChanged = True Then iCountDone = iCountDone + 1 End If End If Next tdf Set prop = Nothing Set tdf = Nothing MsgBox iCountChecked & " tables checked" & vbCrLf & vbCrLf _ & "Reset SubdatasheetName property to [None] in " _ & iCountDone & " tables" _ ,, "Reset Subdatasheet to None" End Sub ' You may freely use and share this code ' provided this license notice and comment lines are not changed; ' code may be modified provided you clearly note your changes. ' You may not sell this code alone, or as part of a collection, ' without my handwritten permission. ' Use at your own risk. ' ~ crystal (strive4peace) www.msaccessgurus.com '*************** Code End *******************************************************
Loop through all tables. If the SubdatasheetName property isn't set to [None] then change it. Count how mamy changes were made.
Click
HERE
to download the zipped BAS file containing the code for SetSubDatasheetNone.
(2 kb, unzips to a BAS file)
This code may be used freely, but you may not sell it in whole or in part. You may include it in applications you develop for others.
For years, I've made a habit of turning off AutoCorrect and setting SubDatasheet to [None] in all tables. I modified code posted by Allen Browne to only change tables that need it, and count them.
here's the link to copy:
http://msaccessgurus.com/VBA/Code/table_SetSubDatasheetNone.htm
Let's communicate, collaborate, and appreciate ... we all get better by sharing.
Email me anytime at info@msAccessGurus
Let's connect and team-develop while we build your application together. As needed, I'll pull in code and features from my vast libraries, cutting out lots of development time.
Email me at info@msAccessGurus
~ crystal