Indexing Tables Tips For Microsoft Access
Don't Index Fields with Lots of Identical Data
Indexed Fields Should Generally Be For A Physical Purpose
Until such times as you are having performance problems, only index those fields where you are joining tables together in queries or where you have to avoid duplicates in a field or group of fields.
Indexes take up space in your database and whenever you save a record, you must also save the index.
Watch Out for Auto Index
If you open up Access and select Tools ... Options and then select the Tables / Queries tag you will find a field called
Auto Index on Import/Create:
If you have any fields in your database with these prefixes or suffixes, you will find that you have indexes on the field whether you like it or not. You may as well clear the "Auto Index on Import/Create" text box on this tab as this is a very unnecessary feature.
The standard keys featured here are
So look out for those fields cause your database will be bigger and slower than you think.
In Access 2007, you can check for and change this setting by viewing these pictures.
If you happen to come across a database that has replication indexes and the database no longer synchronizes with other databases, you will be likely to have fields with replication indexes that are not needed. These may have the name of GUID.
Before you take any of my advice on this issues, BACKUP your database. OK you have done that now you can check if the replication indexes are still in place. If they are, deleting the index will save you a lot of space in big tables. This is quite easy to do.
Now another thing to realize about these replication fields is that they are very large, about 30 characters in size. Multiple this by 100,000 rows and you have a lot of space when compared to a long integer field.
The next step in clearing up
replication index fields from a database is deleting the field itself. This
sounds easy but it isn't as you need to transfer the table to another
database using append query. Anyway there is a good solution, use this
tool from trigemal
FYI: I worked on one database where we reduced the size of the database by 40% by removing the indexes and the GUID fields.
These tips was provided to you by Garry
Robinson - Software Consultant
Get Good Help Here
If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.
About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia