How to Speed Up Queries in Access 2007 by Indexing Tables

How to Speed Up Queries in Access 2007 by Indexing Tables
Page content

Queries and reports are two of the most important features of any Access 2007 database. In fact, these two functions alone make up the most important reasons for creating a database in the first place.

Sometimes, databases become so large that queries and reports can be sped up by organizing the data in the database’s tables. Often, the data in an Access 2007 table becomes unwieldy because so many records are being added, deleted, modified, or otherwise appended. The result can be a mess for both users and Access 2007. The result is slower queries and longer report-generation times. This can spell disaster, especially if the database is used in an e-commerce setting. Longer queries mean customers may be waiting for Access 2007 to retrieve the data requested by the customer, increasing the chances that he/she will not want to wait, potentially losing you a sale.

The solution to unwieldy databases is indexing. By adding an index, you can organize the data within a table to help Access 2007 find the information it needs during queries and the generation of reports. Much like the index of a book, a table index puts information likely needed by the user, next to one another for easier look up.

How an Access 2007 Table Index Works

An Access 2007 table index is a mirror of the table that’s simply sorted by one or more of the tables fields. This makes queries and reports faster because Access 2007 already knows where the information is in the table and can simply go get it without having to search through the entire table looking for it. Again, this is much like the index at the back of a book.

Suppose, for example, that you have a large database of customer information that includes the customers’ names, addresses, cities, states, and phone numbers. Now suppose that you often generate a report to organize the information for a print advertisement you will be sending each customer by mail.

You also want to separate the customers by state because residents of different states will be receiving a slightly different advertisement. By creating an index for the “state” field in the table, all of your Arizona customers, for example, will be next to one another which will speed up queries and reports generated for customers by state.

Of course, you could do the same for “city” by indexing that field instead in case you wanted to query or generate reports based on customer’s city or town. Understand that without an index to help Access 2007 find data, the application would have to search through every record each time a query or report is generated in order to pick out the data requested.

In relatively small Access 2007 database, indexing does not significantly decrease query and report generation times. Indexing is really a function for databases that contain many thousands of records. Still, as your database grows and you notice that your queries and reports are taking longer to complete, it is good to know there is a function that can help speed things up.

Some Hints on Creating Table Indexes in Access 2007

There are a few caveats to creating indexes for an Access 2007 table. First, any field in a table may be indexed except three specific field types. Hyperlink fields cannot be indexed and neither can fields set up to contain a memo field type. In addition, OLE object fields cannot be indexed because there is no way that Access 2007, for example, could sort an OLE field containing say JPG images.

Indexes can have a name that is different from the field name being indexed. This is especially useful if you generate multiple indexes and want to organize them according to the date created or some other pertinent information specific to your database. In fact, you don’t have to name your index at all if you don’t want to. At the creation of an index, Access 2007 automatically names the index for you.

Finally, Access 2007 indexes can be set up to either allow or prevent duplicate entries in a table. This option lets Access 2007 know whether multiple records have the same data in the field being indexed. Of course, by default, primary keys are automatically set to the “no duplicates” option.

Conclusion

Indexing Access 2007 tables organizes data by some chosen field to help speed up queries and reports. Much like the index found at the back of a book, an index allows for faster searches because Access 2007 knows where data is stored without having to “turn every page” each time some data is needed.

Typically, very small databases do not benefit from indexing because it doesn’t take Access long to “turn every page” in a small database, looking for the data it needs to complete a query or generate a report. As your database grows, keep in mind that indexing may help you once your database contains thousands of record.