Table Fields.
You can use the Indexed property to set a single-field index. An index speeds up queries on the indexed fields as well as sorting and grouping operations. For example, if you search for specific employee names in a LastName field, you can create an index for this field.
The Indexed property uses the following settings.
|
Setting |
Description |
|
No |
(Default) No index. |
|
Yes |
(Duplicates OK) The index allows duplicates. |
|
Yes |
(No Duplicates) The index doesn’t allow duplicates. |
You can set this property only in the table’s property sheet. You can also click Indexes on the View menu, or click the Indexes button on the toolbar. Microsoft Access then displays the Indexes window. If you add a single-field index in the Indexes window, Microsoft Access will set the Indexed property to Yes.
In Visual Basic, use the Data Access Objects CreateIndex method to create an index for a field.
Use the Indexed property to find and sort records using a single field in a table. The field can hold either unique or non-unique values. For example, you can create an index on an EmployeeID field in an employees table in which each employee ID is unique or you can create an index on a name field in which some names may be duplicates.
Note You can’t index Memo and OLE Object data type fields.
You can create as many indexes as you need. The indexes are created when you save the table design and are automatically updated when you change or add records. You can add or delete indexes at any time in table Design view.
Tip You can specify text that is commonly used at the beginning or the end of a field name (such as “ID”, “code”, or “num”) for the AutoIndex On Import/Create option on the Tables/Queries tab, available by clicking Options on the Tools menu. When you import data files that contain this text in their field names, Microsoft Access creates an index for these fields.
If the primary key for a table is a single field, Microsoft Access will automatically set the Indexed property for that field to Yes (No Duplicates).
If you want to create multiple-field indexes, use the Indexes window.
CreateIndex Method, Primary Property, Primary Property (Microsoft Office 95 Data Access Reference).