Be Aware of the unnecessary indexes

Access IconMicrosoft Access
Access is one of those applications used by millions of people to create databases. A great application with maximum flexibility. I have met a lot of people who develop databases in Access. Looking at their work I discovered that a lot of people do not realize that there are a lot of unnecessary indexes on their tables. If I ask them about that they often tell me that they did not put that specific index on that specific field. So I tell them about the Access feature that does that for them without them knowing it. Let me explain.

Table design
If you create a table in Access (design view is best to see it happen) you add a fieldname and set a datatype. If you look at the field properties at the bottom of the screen you can see if that field will be indexed or not. Now here’s the catch. If your fieldname ends on any of the following letters Access will autmatically add an index to that field. [ID], [Code], [Num], [Key] This can vary depending on the language you are using.

Let’s take a look at the following scenario:

We design a table with just one field something stupid. So we go to table design and add the first field [ID] set to autonumber. We add another field called [Stupid] and set this to a Yes/No data type. If you do that you will see that Access actually adds an index to the [Stupid] field because the name of this field ends on [ID]. If you don’t know that Access will do this then you will end up with a lot of unnecessary indexes in your table.

You can try this yourself (my office is set to English). Add the following fields to the table in design: Liquid – Yes/No datatype, Fluid – Yes/No datatype, PrivatePostalCode – Text datatype. You will see that you have actually got three fields with indexes without you setting them.

How to avoid this?
Ok you made your point so how can we avoid this? The first remedy would of course be think about your fieldnames carefully. If you do need to add fields which end with the designated letters then go to the Access options screen and disable or remove the options there.

  • Open Access
  • File – Options – Object Designers (view settings in the picture) and change or remove accordingly

In 2007 you will go to Office button – Access Options – Object Designers. In Earlier versions go to “Tools – Options – Table design”.

So concluding: if you don’t want any indexes you did not set check your tables after you are done designing them. Remove any unwanted indexes or change your settings in the Options settings.

Happy programming 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s