As you can see in Figure 4-27, Access 2010 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 15.
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 that 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 2010 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 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.accdb open in Datasheet view. For this table, we 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 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 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
Property | 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. | |
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 set this property automatically 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 2010 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 affect performance negatively.
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 10, and a form that uses a subdatasheet in Chapter 15.
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 Read Only When Disconnected property by default is set to No, which means you can still update or add new records to a table that is linked to a Microsoft SharePoint Services site when you are offline. We’ll discuss working with data in an Access Services application in Chapter 22.