MS Access index

Index is a kind of data structure, a special kind of data structure, designed to improve the speed of data retrieval. ta. icrosoft Access uses indexes in tables because you use indexes in books to find data.

• In some cases, such as primary keys, Access automatically creates an index for you.
• At other times, you may need to create your own indexes.
• The index stores the location of the record based on the field you selected for the index.
• After Access gets the location from the index, it can retrieve the data by moving directly to the correct location.
• In this way, indexing can be significantly faster than scanning all records to find data.
• Indexes can speed up searches in queries, but they can also slow performance when adding or updating records.

Now let's look at what indexes Microsoft Access creates by default, how to create them yourself, and learn how to remove any unnecessary indexes. Open the tblEmployees table in the Access database we created.

• We didn't really index in this database, but that doesn't mean we didn't.
• In fact, any field that is set as the primary key in Access is automatically indexed.
• Access creates additional secondary indexes based on the name of the field.

Now let's go to the file menu and select the options.

You'll see the Access Options window.

Go to The Object Designer and you'll see a section marked AutoIndex when you import/create, and you'll see the ID in the text box; k ey; c ode; N um. B y default, access automatically adds secondary indexes to fields that use the beginning or end of these names, as well as fields that are imported and manually created.

If you want to index any field, you can go to the Fields tab.

Select any fields you want to index, and select the Index check box in the Field Verification section. Y ou can also create or delete alternate options for indexes. You can return to the design view.

You can adjust the index by selecting any field. Y ou can also view their indexes in the field property area below. A ny field that is not selected next to the index indicates that the field is not indexed. t). selecting the other two options - Yes (Repeat OK) and Yes (No Repeat).

The last option is (no repetition) which means that Access will automatically disable duplicate values in the field. Let's now index our last name field.

Let's choose LastName to index and say Yes (Duplicates OK). A ccess creates the index when we save it. Another area where you can view and adjust the table index is the table design area in the Show/Hide group.

If you click the Index button, a special view is displayed showing all indexes created for this table.

Now we have two tblEmployees indexes - it's created automatically based on the primary key field, and we just created an index for the LastName field. These are different ways to handle indexes in Microsoft Access tables.