Solved: Access Index question

[JKwan]

I recently been doing alot of work with Access (I usually do Excel). My question is that when I created an index for my table, does Access use the index automatically? Now, if I run a SQL from Excel to retrieve data into my SS or form, does the index get used? If not, how do I make use of that index?

[hansup]

The db engine will decide whether the index can be used and whether or not it is worthwhile to use the index instead of performing a full table scan.

You can influence the engine's decision by how you design your SQL. For example, with an index on date_field, the first query could use the index but the second can not because the WHERE clause function forces the evaluation of date_field for every row.

SELECT * FROM YourTable
WHERE date_field >= #2011-01-01# AND date_field < #2012-01-01#;

SELECT * FROM YourTable
WHERE Year(date_field) = 2011;

Also the database will examine the index statistics to see whether using it is worthwhile. If there is very little variability in your index values, it may decide there is not enough benefit from using the index ... it will just perform a full table scan instead of first checking the index to find which rows match then retrieving the matching rows from the table.

The index statistics are updated when you compact the database. Compact regularly to give the db engine updated statistics to use when planning how to execute your SQL.

[JKwan]

Thank you for the response. From what you are describing, it is mostly automatic as how the index is applied, which is good to me.

Thanks again.