Understanding Other Table Properties

As you can see in Figure 4–23, Access 2007 provides several additional table properties that you can set in Design view. You can enter a description of the table in the Description property, and you’ll see this description in the Navigation Pane if you ask for 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. If you set Filter On Load to Yes, Access applies the filter you defined when you open the datasheet. 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. You can set the Order By On Load property to Yes to request that Access always applies any Order By specification when opening the datasheet.

Note 

If you apply a filter or specify a sorting sequence when you have the table open in Datasheet view, Access 2007 saves the filter in the Filter property and the sorting sequence in the Order By property. If you have Filter On Load or Order By On Load set to Yes, Access reapplies the previous filter or sort sequence criteria the next time you open the datasheet.

You can find five properties-Subdatasheet Name, Link Child Fields, Link Master Fields, Subdatasheet Height, and Subdatasheet Expanded-that are all related. 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 Contact 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 Housing Reservations sample database, you can see Departments and their Employees, or Employees and their Reservation Requests. Figure 4–24 shows you the Departments table in Housing.accdb open in Datasheet view. For this table, we defined a subdatasheet to show related employee information for each department.

Figure 4–24: The datasheet for the tblDepartments table in the Housing Reservations sample database shows an expanded subdatasheet.

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 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
Open table as spreadsheet

Property Name	Setting	Description
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.name	Uses the selected table or query as the subdatasheet.
Link Child Fields	Name(s) of the foreign key fields(s) in the related table, separated by semicolons	Defines 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 Fields	Name(s) of the primary key field(s) in this table, separated by semicolons	Defines 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 Height	A measurement in inches	If 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 Expanded	Yes or No	If 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 2007 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. We’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.

You can use the Orientation property to specify the reading sequence of the data in Datasheet view. The default in most versions of 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.

The Display Views On SharePoint property by default is set to Follow Database Setting, which means links are created in the views list when this table is upsized to a Windows SharePoint Services site. We’ll discuss upsizing a database to a Windows SharePoint Services site in Chapter 22.