MS Access - Indexing

An index is a data structure, a special data structure designed to improve the speed of data retrieval. If you often search a table or sort its records by a particular field, you can speed up these operations by creating an index for the field. Microsoft Access uses indexes in a table as you use an index in a book to find data.

• In some instances, such as for a primary key, Access automatically creates an index for you.
• At other times, you might want to create an index yourself.
• An index stores the location of records based on the field or fields that you choose to index.
• After Access obtains the location from the index, it can then retrieve the data by moving directly to the correct location.
• In this way, using an index can be considerably faster than scanning through all of the records to find the data.
• Indexes can speed up searches in queries, but they can also slow down performance when adding or updating records.

Let us now look into what indexes Microsoft Access creates by default and how to create them ourselves and learn how to delete any unnecessary indexes. Open the tblEmployees table in Access database which we have created.

• We haven't really played with indexes in this database but that doesn't mean we don't have any.
• In fact, any field that is set as a primary key in Access is automatically indexed.
• Access creates additional secondary indexes depending on the names of your fields.

Let us now go to the File menu and select Options.

You will see the Access Options window.

Go to the Object Designers and you will see a section labeled AutoIndex on Import/Create and in the textbox you will see ID;key;code;num. By default, access automatically adds a secondary index to fields that start or end with these names and that goes for fields you have imported as well as ones you have manually created.

If you want to make any field indexed you can go to the Field tab.

Select any field that you want indexed and check the Indexed checkbox in Field Validation section. You also have alternate options for creating or removing an index. You can go back to the Design View.

You can adjust indexes by selecting any field. You can also see how they are indexed in the field properties area below. Any field that has No selected next to indexed, means there is no index for that given field. You can change that by clicking on the drop-down menu and choosing the other two options — Yes (Duplicate OK) and Yes (No duplicates).

The last option Yes (No Duplicates) means that Access will automatically prohibit duplicate values in that field. Let us now create an index for our last name field.

Let us select LastName to index and say Yes (Duplicates OK). As we save, Access will create that index. Another area where you can view and adjust your indexes for a table is the tables design area in the Show/Hide group.

If you click on this Indexes button that will bring up a special view displaying all the indexes created for this table.

We now have two indexes for tblEmployees —one that was created automatically based on the primary key field and one that we just created for the LastName field. These are the different ways to deal with indexes in Microsoft Access tables.