MS Access - Indexing
Ms access tutorial
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 for 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 when you use an index in a book to find data.
- In some cases, such as 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 (s) you choose to index.
- Once Access gets the location of the index, it can then recover the data by directly moving to the correct location.
- In this way, the Using an index can be considerably faster than going through all the records to find the data.
- Indexes can speed up searches in queries, but they can also slow performance when adding or updating records.
Now let's take a look at what Microsoft Access creates by default and how to create them on our own and learn how to drop unnecessary indexes. Open the tblEmployees table in the Access database we created.
- We haven't really played with indexes in this database, but that doesn't mean we don't.
- In fact, any field defined as primary key in Access is automatically indexed.
- Access creates additional secondary indexes based on the names of your fields.
Now let's go to the File menu and select Options.
You will see the Access Options window.
Navigate to the Designe rs object and you will see a section titled AutoIndex when importing / creating and in the text box you will see ID; key; coded; num. By default, Access automatically adds a secondary index to fields that begin or end with these names and that goes for the fields you imported as well as those you created manually.
If you want to make a field indexed you can go to the Field tab.
Select any field you want to index and check the Indexed box in the Field validation section. You also have other options to create or delete an index. You can return to the Design view .
You can adjust the indexes by selecting any field. You can also see how they are indexed in the field properties area below. Any field for which No is selected next to indexed, means that there is no index for that given field. You can change this 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 this field. Now let's create an index for our name field.
Let's select LastName to index and say Yes (Duplicates OK) . As we save, Access creates this index. Another area where you can show and adjust your indexes for a table is the table design area in the Show / Hide group.
If you click on this Index button that will bring up a special view showing all the indexes created for this table.
We now have two indexes for tblEmployees - one that was created automatically based on of the primary key and the one we just created for the LastName field. Here are the different ways to manage indexes in Microsoft Access tables.