Provided by Allen Browne, February 2004. Updated April 2010.
This article may help if your database is already corrupt. To find out what causes corruption, see Preventing Corruption.
Before doing anything else, make a copy of the corrupted mdb file while Access is NOT running, and without overwriting any earlier backups. This lets you try different approaches and sequences if necessary.
Next, try the built-in repair utility. This very simple solution may work with corrupted indexes, and might even get rid of a corrupted object:
If this does not work, follow the steps for the symptoms of your corruption below.
While developing forms, reports, and the code in their modules, they are likely to corrupt. To work around this, import the other objects into a new database:
If one form or report is corrupt, the process will cease at step 5. You will see the name of the object that has not been imported, and you can then try the import again, skipping the bad form(s)/report(s). With a bit of luck, you may be able to import these from a previous backup.
This can happen if an index corrupts. To address this:
In obstinate cases, you may need to recreate the tables programmatically. More information in knowledgebase article 815280.
If this persists after restarting your computer, the table or its index is corrupt. Try the steps above for a corrupted index.
If that does not solve the problem:
Again, programmatic re-creation may help.
Access uses a pointer to another location for the data in large fields (memo, hyperlink, or OLE Object). If the pointer is written incorrectly, the field displays garbage.
To address this kind of corruption, delete the memo field from your table. Compact to get completely rid of it:
Then create the memo field again. If the data is important, you may be able to link to an older backup (File | Get External | Link in Access 95 - 2003; External Data | Import in Access 2007 and 2010), create a query joining the current and older copy on the primary key, and then change it to an Update query to update the now blank memo field with the contents of the old one.
If the strange characters appear only in the query, not when you view the table, this is not a corruption. This occurs when JET is unable to determine the data type of the query field, and is triggered by lots of situations. Solutions for this non-corruption issue include:
If you receive this error when trying to convert from one version of Access to another, your database is partially corrupt. Decompile a copy of your database.
This is a nasty bug in the early releases of Access 2000, and there is no easy fix. The corruption will not occur if you apply the service packs, or use later versions of Access.
Knowledgebase article 304548 explains that the problem was with version 6.3.91.8 of Vbe6.dll. If you import code from another version (typically Access 97) and close the database without recompiling, your code is hosed.
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
Dirk Goldgar (Microsoft Access MVP) traced this problem back to faulty entries in the MSysAccessObjects table. He reports that the problem can be solved by deleting the faulty entries, and creating a valid primary key.
Download his solution from http://www.datagnostics.com/dtips/fixaoindex.html, or use the use the code (at right) like this:
This corruption of the MSysObjects table occurs in Jet 3.x (Acc 95 or 97). Microsoft released a utility (jetcomp.exe) to address this issue. For more information, see the knowledgebase article for Access 97 or Access 95.
When you compact/repair a database, Access rebuilds the indexes. If it discovers data the violates the index (such as duplicate values in a unique index or primary key), it drops the index. Your table still has all the data, but the index is gone. If you have relationships to other tables that depend on this index to maintain referential integrity, the repair process has to drop those relationships as well.
When this happens, it creates a new table to notify you of the problem. You will find a table with a name such as Compact Errors. The creation date lets you know when Access dropped the index/relations.
To fix this situation, you need to identify the records that violate the index. To find the problem records, use the Find Duplicates Query Wizard (first dialog when you create a new query.) You can then delete the bad records, and mark the field as primary key again.
If a relation was dropped, use the Find Unmatched Query Wizard to identify the bad records in the related query. Delete them or reassign them to the correct key value. You can then create the relation again (Tools menu.)
This is an undocumented technique that may rescue a bad form or report by exporting it to a text file, and instructing Access to recreate it from the text file.
You will receive an error message at Step 3 if Access cannot make sense of the form to export it. You will have to delete the form and re-create it, but if you saved the code at Step 2 you can at least paste that back into the new form's module after you create it.
If Step 3 works without error, there is a good chance the rest of the steps will succeed too.
If none of these steps solve your problem and you need professional help, talk to a professional recovery service such as EverythingAccess. Their repair guide is also a good resource.
To avoid corruption, see Preventing Corruption.
Home | Index of tips | Top |