Part I: Access Building Blocks
72
Indexed
The Indexed property tells Access that you want to use a field as an index in the table. Indexed
fields are internally organized to speed up queries, sorting, and grouping operations. If you intend
to frequently include a certain field in queries (for example, the employee ID or Social Security
number) or if the field is frequently sorted or grouped on reports, you should set its Indexed
property.The valid settings for the Indexed property are as follows:l (^) No: The field is not indexed (default).
l Yes (Duplicates OK): The field is indexed and Access permits duplicate values in the
column. This is the appropriate setting for values such as names, where it is likely that
names like Smith will appear more than once in the table.
l (^) Yes (No Duplicates): The field is indexed and no duplicates are permitted in the col-
umn. Use this setting for data that should be unique within the table, such as Social
Security numbers, employee IDs, and customer numbers.
Indexes are discussed in more detail later in this chapter.
In addition to the primary key, you can index as many fields as necessary to provide optimum per-
formance. Access accepts as many as 32 indexes per table. Keep in mind that each index extracts a
small performance hit as new records are added to the table. Access dynamically updates the
indexing information each time a new record is added. If a table includes an excessive number of
indexes, a noticeable delay might occur as each new record is added.
The Indexed property is set in the field’s Property Sheet or on the table’s Property Sheet. You
must use the table’s Property Sheet to set multi-field indexes.
Cross-Reference
Using the table indexes Property Sheet is discussed in Chapter 3.
The AutoIndex option
The Access Options dialog box (File ➪ Access Options ➪ Object Designers) contains an entry
(AutoIndex on Import/Create) that directs Access to automatically index certain fields as they’re
added to a table’s design. By default, fields that begin or end with ID, key, code, or num (for
example, EmployeeID or TaskCode) are automatically indexed as the field is created. Every time
a new record is added to the table, the field’s value is added to the field’s index. If there are other
field name patterns you’d like Access to automatically index, add new values to the Auto Index
on Import/Create checkbox on the Object Designers tab in the Access Options dialog box (see
Figure 2.21).