How to deal with unwanted or redundant indexes.
While reading my new book, Expert One-on-One Microsoft Access Application Development, Garry Robinson (see my review of his Access Security book in Access Archon #120) wrote to point out that I didn’t mention that giving fields the suffix “ID” can result in an index automatically being created on that field, whether you want it or not. The key field in a table will have an index, of course, but if the table also includes several foreign keys with names ending with “ID”, you probably don’t want indexes on them too, as extra indexes can cause the database to be bigger and slower than it would be otherwise.
You can delete redundant indexes in the Indexes window, opened by clicking the Indexes button for a table opened in design view. To prevent this from happening in future, open the Options dialog and click the Tables/Queries tab. There is a box labeled “AutoIndex on Import/Create” with a list of the prefixes/suffixes that will trigger an auto-index; just clear the box and indexes will no longer be created automatically.
This is one of many useful tips on Garry’s Web site, http://www.vb123.com/.
