MS Access Database Error – AOIndex is not an index in this table

Database corruption is not a new thing for database user and these errors can be major or minor. In this blog I will discuss the solution for MS Access database error “AOIndex is not an index in this table”. When you try to open the database, you will get the following error message on the screen:

'AOIndex' is not an index in this table.

MS Access database has a hidden system table “MSysAccessObjects” with number of records having Null Id and data field and these entries called faulty entries. These fields have no index on this table and do not make sense. Index name of the correct entries is “AOIndex”. This is the main reason of this error. You can try these solutions:

By Creating New database:

• Create a new empty database.
• Import all objects to this new database.
• If you have a problem with one or more tables, create them manually. 
• Transfer the data manually, and RE-IMPORT one table to import the relations. Delete the extra table again.

Note: If you are trying to repair the database then, make a copy of your database before repair it.

FixBadAOIndex Utility: 

This utility is created by Dirk Goldgar (Access MVP) to fix this corruption. You can easily download this FixBadAOIndex utility from author website. 

Note:Do not use this utility to fix other forms of corruption.

An another way to use this utility:

Look at the following code on the below:

Sub FixBadAOIndex(BadDBPath As String)

' <BadDBPath> is the path to the corrupt database.

    Dim dbBad As DAO.Database

    Dim tdf As DAO.TableDef

    Dim ix As DAO.Index

    Set dbBad = DBEngine.OpenDatabase(BadDBPath)

dbBad.Execute "DELETE FROM MSysAccessObjects "& _

        "WHERE ([ID] Is Null) OR ([Data] Is Null)", _

dbFailOnError

    Set tdf = dbBad.TableDefs("MSysAccessObjects")

    Set ix = tdf.CreateIndex("AOIndex")

    With ix

        .Fields.Append .CreateField("ID")

        .Primary = True

    End With

tdf.Indexes.Append ix

    Set tdf = Nothing

dbBad.Close

    Set dbBad = Nothing

End Sub

To use this code, simply follow these simple steps:

• Make a copy of the .mdb file while Access database is not running.
• In a new database, Paste the above code into the module.
• Open the Window with the help of Ctrl+G and enter:     FixBadAOIndex("C:\MyPath\MyFile.mdb")

Use the name of your database in the quotes.

Stellar Phoenix Access Database Repair:

It is a paid tool but I found it very useful for MS Access database repair. With the help of this tool, you can see the preview of your corrupt database. To operate this tool is not a rocket science because it has a user friendly GUI. Have a look on its GUI:

To repair the database, Click on the Select Database option. After selecting this option, you will get the following dialog box on the screen. 

Now select the corrupt .mdb file and click on the Repair button. So it is a paid and secure way to repair your corrupt MS Access database.

So we have seen the most useful solutions to solve this error. I hope this article would be helpful for every reader and able to solve this error. Before using any solution, take an updated backup of your MS Access database.