This article applies only to a Microsoft Access database (.mdb).
Setting the Subdatasheet Name property manuallyTo set the Subdatasheet Name property manually, follow these steps.
NoteThese steps only work on Access 2002.
- In the back-end database, open a table in Design view.
- On the View menu, click
- Set the Subdatasheet Name property to
- Save and then close the table.
Setting the Subdatasheet Name property for all tables by using codeYou can use a Visual Basic for Applications function to automatically set the Subdatasheet Name property for all nonsystem tables in a database to [NONE]. To do so, follow these steps:
- Open the back-end database.
- On the Database window, click Modules, and then click New.
- On the Tools menu, click
References. Make sure the Microsoft DAO 3.6 Object Library check box is selected, and then click OK.
- Type or paste the following code into the new module.
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer
On Error GoTo tagError
Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[None]"
rplpropValue = "[Auto]"
intCount = 0
For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intCount = intCount + 1
If intCount > 0 Then
MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
intCount = intCount + 1
MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets routine."
- In the Immediate window, type the following text, and then press ENTER to run the function: Note that after a short time, if all the tables are not already updated, you receive a message box that tells you that the SubDataSheetName property for <NumberOfTablesUpdated> non-system tables has been updated to [NONE].
The principal table in a one-to-many relationship (the table on the "one" side of the equation) can have its Subdatasheet Name property set to [None], in which case subdatasheets are not displayed. Or its Subdatasheet Name property can be set to the name of a particular related table, or to [Auto]. If the property is set to [Auto], you are able to select the related table whose records you want to see when you click the expand indicator of a record in the principal table. When you set the property to [Auto], this can reduce performance noticeably, particularly on older computers, when the database uses a large number of linked tables. This behavior does not occur when all tables are present in the same database.
Because the issue is slow performance, factors such as processor speed and available system resources may cause a database that performs adequately on one computer to perform slowly on a different computer.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.