The Blog

Microsoft Access “ID is not an index in this table”

Microsoft Access

Problem

Recently I got a call from a client that was having issues with their Microsoft Access database. They told me that every time they opened the application, it would tell them that there was an error/problem with the database and that it had to back itself up and attempt to repair itself. As soon as it repaired itself, it would give a whole bunch of “ID is not an index in this table” error messages to which they had to click “Ok” a bunch of times to get rid of.

After a short while of going through this procedure, about 10 copies of the database were created, and named “backup of backup of backup….myfile.accdb”

Solution

At first, I tried to “Compact and Repair” the database. That didn’t work. Next, I tried creating a new database and importing the tables, forms, reports and queries from the corrupted database. When I tried the import, I just got a bunch of “ID is not an index in this table” messages and nothing more. The import would stop at this point.

What eventually worked was to open the corrupted database and manually export each object from the corrupted database to a brand new database. I was able to do this with the export tools in the ribbon bar and by selecting “Access” as my export option. I did have to create the new, blank database first; the database to export all of the data to.

Unfortunately, I could only export one object at a time. So it took a while to do; there were about 50-60 objects to move.

Notes

Here’s a few things to keep in mind with this problem.

  • The database was being shared in a multi-user environment over a network. One copy of the database existed on a network drive and was being access by 4-5 people at a time.
  • Different versions of Microsoft Access were being used. Some users were running Access 2007 and others Access 2010. I don’t know if this helped create the corruption.
  • The error seemed to make reference to VB Code. However, I could not open the Visual Basic editor to see if anything was wrong (it wouldn’t open when I clicked on it).

Tags: ,

No comments yet.

Leave a Comment

Remember to play nicely folks, nobody likes a troll.

You must be logged in to post a comment.