• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Creating Your Database and Ta... > Understanding Other Table Properties

Understanding Other Table Properties

As you can see in Figure 4-27, Access provides several additional table properties that you can set in Design view. You can enter a description of the table on the first line, and you’ll see this description in the Database window if you select the details view. For Default View, you can choose from Datasheet (the default), PivotTable, or PivotChart. You can read more about PivotTable and PivotChart views in Chapter 13, “Advanced Form Design.”

The Filter property lets you predefine criteria to limit the data displayed in the Datasheet view of this table. You can use Order By to define one or more fields that define the default display sequence of rows in this table when in Datasheet view. If you don’t define an Order By property, Access displays the rows in primary key sequence.

The next five properties—Subdatasheet Name, Link Child Fields, Link Master Fields, Subdatasheet Height, and Subdatasheet Expanded—are all related. Microsoft Access 2000 introduced a feature that lets you see information from related tables when you view the datasheet of a table. For example, in the Contacts Tracking database you have been building, you can set the Subdatasheet properties in the definition of Contacts to also show you related information from ContactEvents or ContactProducts. In the Proseware Housing Reservations sample database, you can see Departments and their Employees or Employees and their Reservation Requests. Figure 4-28 shows you the Departments table in Housing.mdb open in Datasheet view. For this table, I defined a subdatasheet to show related Employee information for each department.

Figure 4-28. The datasheet for the Departments table in the Proseware Housing Reservations sample database shows expanded subdatasheets.


Notice the small plus and minus signs at the beginning of each department row. Click on a plus sign to expand the subdatasheet to show related employees. Click on the minus sign to shrink the subdatasheet and show only department information. Table 4-8 on the next page explains each of the Table Property settings that you can specify to attach a subdatasheet to a table.

Table 4-8. Table Properties for Defining a Subdatasheet
Property NameSettingDescription
Subdatasheet Name[Auto]Creates a subdatasheet using the first table that has a many relationship defined with this table.
 [None]Turns off the subdatasheet feature.
 Table. name or Query. nameUses the selected table or query as the subdatasheet.
Link Child FieldsName(s) of the foreign key field(s) in the related table, separated by semicolonsDefines the fields in the subdatasheet table or query that match the primary key fields in this table. When you choose a table or query for the Subdatasheet Name property, Access uses an available relationship definition or matching field names and data types to automatically set this property for you. You can correct this setting if Access has guessed wrong.
Link Master FieldsName(s) of the primary key field(s) in this table, separated by semicolonsDefines the primary key fields that Access uses to link to the subdatasheet table or query. When you choose a table or query for the Subdatasheet Name property, Access uses an available relationship definition or matching field names and data types to automatically set this property for you. You can correct this setting if Access has guessed wrong.
Subdatasheet HeightA measurement in inchesIf you specify zero (the default), each subdatasheet expands to show all available rows when opened. When you specify a nonzero value, the subdatasheet window opens to the height you specify. If the height is insufficient to display all rows, a scroll bar appears to allow you to look at all the rows.
Subdatasheet ExpandedYes or NoIf you specify Yes, all subdatasheets appear expanded when you open the table datasheet. No is the default.


Inside Out: Don’t set subdatasheet properties in a table

For a production application, it’s a good idea to set Subdatasheet Name in all your tables to [None]. First, when Access opens your table, it must not only fetch the rows from the table but also fetch the rows defined in the subdatasheet. Adding a subdatasheet to a large table can negatively impact performance.

Also, any production application should not allow the user to see table or query datasheets because you cannot enforce complex business rules. Any data validation in a table or query datasheet depends entirely on the validation and referential integrity rules defined for your tables because you cannot define any Visual Basic code behind tables or queries.

However, you might find the table and query subdatasheets feature useful in your own personal databases. I’ll show you how to build a query with a subdatasheet in Chapter 8, “Building Complex Queries,” and a form that uses a subdatasheet in Chapter 13, “Advanced Form Design.”


The last property available in the Table Properties window is Orientation. The default in most versions of Microsoft Access is Left-to-Right. In versions that support a language that is normally read right to left, the default is Right-to-Left. When you use Right-to-Left, field and table captions appear right-justified, the field order is right to left, and the tab sequence proceeds right to left.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint