What is a subdatasheet?

Question

The table property sheet mentions, in three places, "Subdatasheet". I don't know what this is and neither the "on-line" help nor the "MS Access 2013 Bible" is at all forthcoming on the point. Can someone enlighten me?

Answer 1

Hi. A Subdatasheet can display any child records for each parent record. When you're viewing a table, you might see a plus "+" sign to the left of each record. And if you click on it, a sub-datasheet opens below the record to show the related records from a child table.

Hope that helps...

Answer 2

It's an automated query that joins a child table to the parent table so you can see related records. The customer ID would have a subdatasheet of all the orders that customer made. It's like a tree view.

Frankly, it is bloat that greatly slows down performance. And since users shouldn't have direct access to tables I've never had a use for subdatasheets.

You can turn them all off by running a function I wrote, but with each new table you create you have to remember to turn the darn thing off on that table.

Answer 3

Thanks to all who have replied. I will have to check up on "parent/child" but you have pointed me in the right direction.

Parent/child is just a way to speak of related records. The parent has to exist before the child can be created. You can't have an order record if you don't know which customer ordered it.

Answer 4

Thanks to all who have replied. I will have to check up on "parent/child" but you have pointed me in the right direction.

Answer 5

I forgot to post the function.

Public Function dev_TbrTurnOffSubDataSheets()

'Purpose  : Access sets all tables' SubDataSheets to Auto which slows up loading time.

'           This function will set the property to [none].

'DateTime : 1/14/2004 09:20

'Author   : Bill Mosca

    Dim db As DAO.Database

    Dim prop As DAO.Property

    Dim propName As String, propVal As String

    Dim propType As Integer, i As Integer

    On Error Resume Next

    Set db = CurrentDb

    propName = "SubDataSheetName"

    propType = 10

    propVal = "[NONE]"

    For i = 0 To db.TableDefs.Count - 1

        If (db.TableDefs(i).Attributes And dbSystemObject) = 0 Then

            If db.TableDefs(i).Properties(propName).Value <> propVal Then

                db.TableDefs(i).Properties(propName).Value = propVal

            End If

            If Err.Number = 3270 Then

                Err.Clear

                Set prop = db.TableDefs(i).CreateProperty(propName)

                prop.Type = propType

                prop.Value = propVal

                db.TableDefs(i).Properties.Append prop

            Else

                If Err.Number <> 0 Then

                    Resume Next

                End If

            End If

        End If

    Next i

    MsgBox "The " & propName & _

            " value for all non-system tables has been updated to " & propVal & "."

    Set prop = Nothing

    Set db = Nothing

End Function