Microsoft Access Tips for Serious Users

Provided by Allen Browne, February 2004. Updated April 2010.


Contents:

Cannot open form or report

Number of records varies

#Deleted in some rows

Strange characters in Memo field

Error Loading <Form_FormNamE>

Error Accessing File

AOIndex is not an Index

<DatabaseName> isnt an index

Key index/relationships gone

SaveAsText / LoadFromText

Other Resources

Recovering from corruption

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.

Symptom: Cannot open a form or report

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:

  1. Create a new database.
  2. Turn off the Name AutoCorrect check boxes: For details of problems this mis-feature causes, see Failures caused by Name Auto-Correct.
  3. Import the tables, or link them if the database is split.
    In Access 2007 or 2010, choose External Data | Import | Access.
    n Access 95 - 2003, choose Get External on the File menu.
  4. Import the other objects (queries, forms, reports, macros, modules.)
  5. Set minimal References under Tools | References (from the code window).
  6. Compile (Debug menu, from the code window).

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.

Symptom: Number of records varies, depending how the data is sorted

This can happen if an index corrupts. To address this:

  1. In the Relationships window, delete any relationships this table is involved in.
    In Access 2007 or 2010, click Database Tools on the ribbon, and choose Relationships.
    In Access 95 - 2003, choose Relationships from the Tools menu.
  2. Open the table in design view. Open the Indexes box, and delete all indexes. Save. Close.
    In Access 2007 or 2010, Indexes is on the Table Tools ribbon.
    In Access 95 - 2003, it is on the View menu.
  3. Select the table in the Database window (Access 95 - 2003) or Nav Pane (Access 2007 and later.) Copy and Paste, supplying a new name, and choosing Structure Only.
  4. Create a query into the problem table. Check you see all records (sorting if necessary). Change it to an Append query, telling Access to append to the new table. Run the query. (Append is on the Query Tools ribbon in Access 2007 and 2010, or Query menu in Access 95 - 2003.)
  5. Check that all the data is in the new table, and then delete the old table.
  6. Compact the database to get rid of any reference to the problem table:
  7. Rename the new table so it has the name of the old table.
  8. Recreate the indexes and relationships you destroyed above.

In obstinate cases, you may need to recreate the tables programmatically. More information in knowledgebase article 815280.

Symptom: Some table rows show #Deleted

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:

  1. Create a query into the table.
  2. Attempt to exclude the corrupted rows. For example, if the corrupted row is between ID 25 and 27, try criteria of:
        <= 25 Or >= 27
  3. Once you have the best range of uncorrupted records you can retrieve, change it to a Make Table query. Make Table is on Query menu in Access 95 - 2003; in Access 2007 or 2010, it is on the Query Tools ribbon under Query Type.
  4. Run the query to create the new table.
  5. Delete the old table.
  6. Compact the database to get rid of any reference to the problem table:
  7. Rename the new table to the name of the old one.
  8. Recreate the indexes and relationships you destroyed above. If you are unable to create a relationship, use the Unmatched Query Wizard to identify which records are missing.

Again, programmatic re-creation may help.

Symptom: Memo field contains strange characters.

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:

Symptom: "An error occurred while loading Form_FormName"

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.

  1. Make a copy of the mdb file while Access is not running.
  2. Compact:
  3. Close Access. Open a command prompt, and enter something like this. It is one line, and include the quotes:
       "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\My Documents\MyDatabase.mdb"
  4. Compact again.
  5. Try the conversion again.

Symptom: "Error Accessing File. Network Connect May Have Been Lost"

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

Symptom: "AOIndex is not an index in this table"

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:

  1. Make a copy of the mdb file while Access is not running.
  2. In another database, paste the function at right into a module.
  3. Open the Immediate Window (Ctrl+G), and enter:
        FixBadAOIndex("C:\MyPath\MyFile.mdb")
    using your database name in the quotes.

Symptom: "<Database Name> Isn't an Index in This Table"

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.

Symptom: Key field is no longer primary key, and relationships are gone

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.)

SaveAsText / LoadFromText

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.

  1. Make a backup of your database.
  2. Open the code window (Ctrl+G) and look through the modules (Windows menu.)
    If you can see a module for this form/report, copy the text out to Notepad, and save it.
  3. Open the Immediate Window (Ctrl+G), and export the form/report as a text file. Enter something like this:
        SaveAsText acForm, "Form1", "C:\MyFolder\Form1.txt"
    substituting your form name for Form1 and your directory for MyFolder.  Use acReport if the problem object is a report.
  4. Verify that the text file was created in the folder you specified.
  5. Delete the form from the database.
    Select it in the Navigation Pane (Access 2007 or later) or Database Window (previous versions), and press Del.
  6. Compact the database:
  7. Open the Immediate Window (Ctrl+G). Enter something like this:
        LoadFromText acForm, "Form1", "C:\MyFolder\Form1.txt"
    substituting the same things you did at Step 3 above.

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.

Other Resources

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