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.

Top 10 Tips to resolve Access Database error message

Every now and then Access DBAs may get some error messages in their Access database that they have not thought. They will try to resolve the error messages but do not know where to start. If you are also a victim of Access error messages then this article is for you, try below steps and I am quite sure you will find a resolution for the error message.

1. Compile the Modules: Sometimes a compile error in a single module can cause the problem in other module so it is a good idea to check all the modules are compiling successfully. You can do this by following procedure:

Click on the Debug->Compile from any module.

2. Compact & Repair Database: Sometimes compacting & Repairing can fix the problem, and it is a good idea to perform compact & repair on the regular interval to avoid such types of problem. You can do this by following procedure:

Tools->Database Utilities->Compact & Repair

Note: Make a backup of database before compact & repair.

3. Restart the Machine: Sometimes problem has been occurred due to memory problem so it is a good idea to shutdown your machine for few seconds and turns it on again. Now try to open your Access database, you may find that problem is solved.

4. Decompile & Recompile the Database: Sometimes Access generates error message due to wrong residual compiled code so it is the better idea to decompile & recompile the database.

5. Import the Database into a New Database: If above resolutions do not work for you then try to import the entire object from an old database into a newly created database. You can do this by following procedure:

Create a new database-> Click on the File menu-> Get External data -> now click on the Import-> select old database file-> Import

6. Restore from Backup: Restoring from backup is the best option to get back all your data from the database. If you have an updated backup of your database then it is the right time to use it.

7. Repair/Reinstall Access Database: Sometimes MS Access generates error messages due to missing or corrupt libraries so it is better idea to repair or reinstall the Access database. You can repair Access database from the control panel while re-installation can be done with the help of installation disk.

8. Update Latest Updates: It might be possible that you are getting error message due to missing updates so try to update all the latest updates for your Access database version. You can download all the latest update from the Microsoft website http://office.microsoft.com.

9. Get Professional Help: There are some people on the internet who will provide help for you without any cost. Popular websites for Access help are utteraccess.com, access-programmers.co.uk, social.msdn.microsoft.com, accessforums.net, and many more.

10. Addition Tips: There are some data recovery companies that offer Access database repair software to repair corrupt Access database. Give them a try and see what they can do for you.

Note: Always try free demo version of any software before buy.  

Need to Improve the Performance of an Access Database

Are you facing performance issues with your Access database? If Yes then splitting an Access database is the best solution for you. Split an Access database is a process to reorganize your database into two files; one is front-end database and another is back-end database. Back-end database contains all the tables of database while front-end database contains all the other objects like forms, queries and reports. Users have their own local-copy of back-end database to interact with the front-end database.

Have you decided to split the database? Before you start splitting an access database; make sure following points:

1. Backup the database: Backup helps you in the restoring of database in case you want to revert back.
2. Stop all the operation of database: User database modifications will not be saved during split.
3. Split a copied database: If database is on the network drive then copy it on the local drive of your computer/laptop and start with the copied database.

How to do it? You can split an Access database by following below steps:

• Open the copied database from your local drive where you have saved the copied database.
• Go to the Database Tool->Move data group and click on the Access database.
• Database splitter wizard will start.
• Now click on the split database.
• Give the name, file type & location for the back-end database file.
• Done message will be display.

Note: If your database is password protected, the new back-end database will be created without password and accessible to all the users. You will need to create a new password after split is done.

Benefits of it: It will improve the performance of your database along with availability, security, reliability & flexibility.

Availability: Now your database users can edit the data more quickly because only data is available on the network drive.

Reliability: Now your database is more reliable than ever before. If a user encounters any problem and database closes suddenly then corruption is limited to that user only because he was using the back-end database file with the linked table.

Flexibility: Now your users can develop their own forms, reports & queries without affecting the other database users.

Alternative: If you don't want to split your database to gain high performance then you have other options too.

1. Share your database using SharePoint Site.
2. Use your Access database with a database server like SQL server

Resolving MS Access 2003 error "unrecognized database format"

Corruption in Access database mostly results into the error messages which appear during the database mounting. Error messages makes database inaccessible or un-mountable. Few situation under which MS Access database gets corrupted are improper system shutdown, virus attach, faulty hardware, software malfunction and many more. The damage created by error messages can be handle by using an updated backup. Most of the Access database administrators have the backup of database to handle these types of corruption issue but there are some database administrators also; who forget to create the backup of database. In such situation, database administrator need to use 3^rd party access database repair tool to repair corrupt Access database files.

Consider a practical scenario wherein database administrator is trying to open the database table and got below error message:

"unrecognized database format" along with the name of database

Possible Cause for Error Message: The root cause of above error message is that Access database has been corrupted.

How to Resolve: Database administrator can resolve above error message by following methods:

Method 1: Delete all Link Tables

If it is split database wherein tables are present is the back-end database and forms, reports & queries are present is the front-end database then back-end database is corrupt. Try to open back-end database, if it lets you to see the tables then open front-end database and delete all the table links. After deleting the links use the file and go to the Get external data and re-link the table again.

Method 2: Compact & Repair Database

If it is not a split database then user can resolve above error message by following steps:

• Close all the Access database files.
• Go-to the Tools menu and select Database Utilities.
• Now point to the Compact & Repair Database.
• Select your desired database file that you want to repair.
• Give the name & location to save the compacted database and click on the save button.

Method 3: Restore from Backup

If you are still unable to resolve above error message by performing two methods then take a sign of relief that you have an updated backup of the database.

• Create a new blank empty database.
• Import all the tables & objects from the backup copy.
• Done

Method 4: 3^rd party Access Repair Program

If you don't have backup or old backup then it is the right time to use 3^rdparty Access database repair program. There is so many such types of programs available on the Internet that claim they can recover the data from corrupt tables. Give them a try and see what they can do for you. 

Tips to Recover Data from Damaged Access Table

Microsoft Access is a part of MS office and used to store data by several organizations. It uses tables to store records. MS Access tables may get corrupt due to several reasons like faulty hardware, virus attack, fatal system errors, power outage and many more. Owning to these reasons, you may want to recover data from corrupt tables. This blog post can surely help you in recovery of data from corrupt tables.

Steps to Recover: Perform following steps for recovery of data from a corrupt MS Access tables:

1. First of all make a copy of your damaged Access database table.
2. Now create a new MS Access table
3. Paste the structure only of damaged table into new table
4. Now you have two tables; damaged table & new table with same structure
5. Open both the table so that you can view both the tables
6. Switch to the damaged table.
7. On the Edit menu, click Copy to copy the records from the damaged table
8. Now switch to the new table, put the cursor in the same location that the data was located in the damaged table. On the Edit menu, click Paste.
9. Perform this operation for all the data and test after each copy & paste operation
10. After moving all the records into a new table, create a new blank database in MS Access with different name.
11. Now import all the undamaged database objects & new table into newly created blank Access database.
12. Close the MS Access.
13. Rename the damaged Access database and move it to the new location. If there is .ldf file of corrupt database exist then delete it first and then after move it to new location.
14. Now rename the newly created database with the name of corrupt database.

Additional Tips

• Always create a copy of damaged Access database to avoid irrecoverable damage during above steps.
• Test recovered Access database before implementing it to production environment.
• Don't delete copy of damaged database until recovery is complete.

Applied: You can apply all the above steps for MS Access 2000, 2002 & 2003.