If an Microsoft Access/Jet index is a duplicate of another index then Microsoft Access/Jet does not take any extra space.  

(Page updated 2010-09-27)

[ Main | Access | Tips ]

Download the TestDuplicateIndexBloating.zip files and compact the Microsoft Access MDB. Look at the size. Open the table and see the second index based on text.  Delete the second index and compact again.  Look at the size. Add an index based on that field and look at the size.

Now add a descending index based on the text field and you will see the Access MDB increase 208 kb.

There was a KB article a number of years ago stating that there was a bug in a particular Service Pack of Jet. But I can't find that KB article now. If two indexes were identical except one was descending then the bug was that the descending index wasn't created and thus performance or viewing data suffered.

I took that to assume that Jet was not actually creating new indexes if the indexes were identical.  Now I never actually never *knew* this until I tried it on the MDB.   But that was my assumption. Just never bothered researching it as it had never come up in conversation before.

Now the duplicate index does take up one of the 32 index slots.  Thus leading to the dreaded "The operation filed. There are too many indexes on table '<table name>'. Delete some of the index on the table and try the operation again." error message.

I have a tool to display and remove duplicate indexes but I have to polish it up a little.  Email me if you are interested in it.

The below screen shows the table I had the most trouble with in one system I designed and created.  Click on the image to enlarge the graphic.

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available