SubDataSheets (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 08:36
Joined
Dec 20, 2007
Messages
2,061
Who likes them?
Who doesn't?

Why or why not?

Thanks
 

strive4peace

AWF VIP
Local time
Today, 07:36
Joined
Apr 3, 2020
Messages
1,003
hi Thales,

they really slow performance, and have other undesirable effects -- better to make queries to get related information. I have code to remove them:

Rich (BB code):
Public Sub SetSubDatasheetNone()
  
   'crystal (strive4peace) 061027, 160905, 200423
   'strive4peace
   'based on code written by Allen Browne
 
   '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
  
   'cheap but it works <g>
   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 need to turn of AutoCorrect to get changes to stay though -- at least you used too. I always turn that off anyway, to avoid that can of worms too.
 
Last edited:

Minty

AWF VIP
Local time
Today, 12:36
Joined
Jul 26, 2013
Messages
10,354
I think they will also give you issues if you ever upsize to SQL Server or something similar, it won't understand them.
 

Thales750

Formerly Jsanders
Local time
Today, 08:36
Joined
Dec 20, 2007
Messages
2,061
This all got started because we seem to be the only developers that don't have a tool for showing expanded list with indentions.
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
As well as slowing down performance, they can be very confusing for end users, especially where the subdatasheets are several layers deep.
Best avoided.
 

zeroaccess

Active member
Local time
Today, 07:36
Joined
Jan 30, 2020
Messages
671
End users should never see the tables...

As a developer, I have found scenarios where turning them on to inspect data relationships between tables was a huge help. Pretty sure that's why they're there.

Otherwise it really doesn't matter once your app is up and running.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:36
Joined
Jan 20, 2009
Messages
12,849
I sometimes use them for myself when directly looking at table when troubleshooting.
 

strive4peace

AWF VIP
Local time
Today, 07:36
Joined
Apr 3, 2020
Messages
1,003
Subdatasheets are helpful when entering and viewing data directly in tables without forms or queries, but users shouldn't do this! Maybe in the beginning, when a developer is building a system and entering test information, before very much is there, it is ok to use them. They can be handy! Once forms are built to control the information, subdatasheets should be removed. Especially as a database gets lots of records.

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.

To each their own though. This is my opinion, and the way I like to do it for myself, and what I teach to those that I help to build applications.
 
Last edited:

zeroaccess

Active member
Local time
Today, 07:36
Joined
Jan 30, 2020
Messages
671
I thought subdatasheets only load when a table is opened directly?
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
Well if the records aren't in my recordset, they shouldn't load.

Sorry but that's not true either
If you have created subdatasheets, these are automatically created in your datasheet forms.
Here's an example I created for this thread with three subdatasheets. It illustrates why these can be confusing to users

The form recordsource is the 'master table' only

Capture.PNG
 

strive4peace

AWF VIP
Local time
Today, 07:36
Joined
Apr 3, 2020
Messages
1,003
and why they're particularly bad in big tables with lots of records -- records that aren't even showing on the screen are busy loading all that other information
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
Yes I agree totally.
The JSON app I showed is the one exception where I allow subdatasheets. It is intended for developers only
It is used to analyse & transform downloaded JSON files into normalised tables and create relationships between them.
In one fairly extreme case, the example JSON file has 7 subarrays resulting in a main table with 7 sub tables (subdatasheets).
Here is part of that structure - there are only two records in the main table in this case

Capture.PNG
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 07:36
Joined
Apr 3, 2020
Messages
1,003
Had subatasheets been implemented differently, they might have been a good idea. As is, they're designed for those who don't really know what they're doing to use -- and they can create problems that newcomers don't know how to fix, or even understand why they're happening
 

zeroaccess

Active member
Local time
Today, 07:36
Joined
Jan 30, 2020
Messages
671
Hmm, I haven't had that issue. Turning them on and off has no effect on my datasheet split forms. They only appear when opening tables directly, which are hidden. For the record (ha!), my forms are based on queries.
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
Hmm, I haven't had that issue. Turning them on and off has no effect on my datasheet split forms. They only appear when opening tables directly, which are hidden. For the record (ha!), my forms are based on queries.
As the forms are based on queries, you won't see the subdatasheets.
 

strive4peace

AWF VIP
Local time
Today, 07:36
Joined
Apr 3, 2020
Messages
1,003
adding on ... my understanding is that just because you don't see them doesn't mean they aren't there! (by that, I mean being generated)
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
Hi Crystal
I was also going to say that I thought that to be the case...but had no evidence to support it.
Signing off again for a while and returning to DevCon for the final session which includes yourself.
 

Users who are viewing this thread

Top Bottom