Indexes in MS Access

Hello Everybody,

This article is just to give an idea about indexes in MS Access database.

Indexes in MS Access

Why we need Indexing?

Indexes in any database (MS Access, Sql server, Oracle) are most important thing to deal with. Especially more the data in the tables of our database, it’s hard for DB to search or refine (filter) the data’s. To overcome this there is some thing called Indexing in the database. Indexes of fields in the table, will able to find data’s in those fields very quickly. Index can drastically increase the performance of DB application. Indexing here is similar to indexing of book contents.

How to create an Index in MS Access?

For creating an index on a field, first you need to open a table (not linked table) in design view. Select the field that you want to create an index. Then go to “General” tab, there you will find the “Indexed” property. Select the property value as “Yes (Duplicates OK)” or “Yes (No Duplicates)”. “No” value means no indexing. By changing the value from “No” to any one of the “Yes” value is completion of index creation.
Primary key automatically indexed to “Yes (No Duplicates)” and for Primary key the index value must be “Yes (No Duplicates)”. For Foreign key any one of the “Yes” value.

Creating Index on Multiple Fields :

In some scenarios, we often use few criteria’s on multiple fields of a table with large data. It’s better to set index for the multiple fields to improve the performance here.
To do this, open a table in design view, click “View” in menu bar and then click “Indexes”, it will open an “Indexes” form.
In “Indexes” form, in the first line give the index name, select the field, then select the Sort order. After that in the next line select the field, and then select the Sort order, you can set up to 10 fields for the index on multiple fields.
Below screen shot shows Single and multiple Index.

Auto Index in MS Access:

We can see some strange list “ID; key; code; num” on “Tables/Queries” tab.
You can find this list by navigating
Click “Tools” in menu bar --> click on “Options” --> “Tables/Queries” tab --> Auto Index on Import/Create.

List value here indicates, if any field name starts or ends with this “ID; key; code; num” values, then Access will automatically create an index for that field. Here you can add your own list. If you don’t want you can delete this list.

Limitation of Indexes in MS Access:

1. Up to 10 fields are allowed for setting an index on multiple fields.


2. Total number of indexes on a table is 32


3. More number of indexes will slow down the speed of updates on your records.

Hope this article is helpful for you. Please leave your comments.

Thanks & regards,

Karthick

Clock in Scatter Chart

Hello Everybody,

Clock (without using Timer) created in Scatter chart (Excel Chart), yeah its true. I got inspired from the work “Fireworks in Scatter chart” done by Excel MVP “Andy Pope” (Chart Genius).

I have used the same logic to create clock in scatter chart, that I was used in “JAVA” language Clock in Java Applet”. Here I have used sine and cosine function to draw the outline for the chart, which is based on radius value in the sheet “Scatter Clock” (in the Attached file).

Please download the file “Download Scatter Chart” here.
Based on X and Y series, i have acheived the thing here.

If any queries, please free to mail me or add comment to this post.

Thanks & Regards,
Karthick