Fixing a Corrupt or Problem Access Database v4.5

If you are getting strange errors or the database just won’t work, you can try the following fixes. Remember that nothing beats a good backup when stuff happens.

Note: I originally wrote the most of this information based on Access 97. There are differences with other versions of Access.

1.…..FIRST MAKE A BACKUP COPY OF THE DATABASE. ‘nuff said.

2.Can’t even open the database.

3.Repair the Database.

4.Decompile and Recompile the Database.

5.References.

6.Memo and OLE Object fields in tables.

7.Everything works except for a form or report.

8.Upgrading to fix corruption.

9.Getting Hexed.

10.Repair using ODBC Sources.

11.Possibly saving the data in a corrupt database.

12.When a Database Seems Corrupt But Isn't.

13.When all else fails, I hope that you have a good, recent backup.

14.How to stop databases from corrupting in the first place.

14.1.Install the latest software patches.

14.2.The database is used by more than one person on a network.

14.3.Split the database

14.4.Networks

14.5.Hardware

14.6.Turn off Compact on Close

14.7.Do NOT put Access databases on compressed drives.

15.Other resources:

  1. …..FIRST MAKE A BACKUP COPY OF THE DATABASE. ‘nuff said.
  2. Can’t even open the database.
  3. Can you open any other databases?
  4. If not, something is wrong with your computer and not the database.
  5. If Access opens but the database causes an error, it could be the database.
  6. Try opening the database while holding down the Shift key. This bypasses any code that runs on startup like an Autoexec macro or startup option. If this works, you may have a corrupt opening form or code.
  7. Still no joy? See if there is file named *.ldb where * is the name of your database. It would be in the same directory or folder. If so, delete it.
  8. Try moving the database to another drive such as off the network to a local hard drive or the hard drive out to the network. Watch out for broken linked tables.
  9. If that doesn’t work, repair the database as described below.
  10. If that still doesn’t work, open a new database and import all the tables, forms, reports, queries, macros, and modules. Also import Import/Export Specs and Menu; Menus and Toolbars; and Relationships.
  11. You will need to go to Tools, Options, View Tab, and make sure Hidden Objects and System Objects are checked to see everything.
  12. Import only a few items at a time.
  13. If an item won’t import, it is probably corrupt.
  14. Replace the corrupt object from a recent backup (you do have backups?) OR
  15. A last chance procedure from David Hare-Scott.
  16. Move code in corrupt form to a safe place like a Word file.
  17. Remove code module from form.
  18. Import again.
  19. Restore module.
  20. Restore code.

  1. Repair the Database.
  2. Get everyone off the database.
  3. With JetComp.
  4. The Jetcomp utility is a stand-alone program that does a much better job of repairing and compacting Access databases.
  5. Microsoft recommends using it instead of the built-in Access compact and repair utilities.
  6. You can get the Jetcomp utility at:

3.2.1.2.1.Note: The latest version of JetComp (dates 8/11/99) works for Access 2000 databases and all previous versions.

3.2.1.2.2.The knowledgeable David Hare-Scott of Syncretic Solutions Pty Ltd () offers the following warning on JetComp:

In JetComp there is an option to set the format of the destination database as JET3 or JET4. If you repair an A97 database with JET4, you won’t be able to open it with A97.

Jetcomp defaults to JET4, which is silly design, it’s a case where there should be no default, forcing the user to actively choose before going on.

3.2.1.2.3.Note: I couldn’t get the latest JetComp to work on my Office 97 equipped computer. I kept getting a MSJET40.DLL missing error. I just went back to using the older Jetcomp. (jsw)

3.2.2.Go to Windows Explorer and find the problem database mdb file(s).

3.2.3.Slightly rename this mdb file (i.e. Maillog.mdb to xMaillog.mdb).

3.2.4.Run JETCOMP.exe.

3.2.5.Within Jetcomp, open the problem database that you just renamed.

3.2.6.Put the original name of the database and the path in the Destination box. Something like J:\Database\Mail Log\Maillog.mdb. This allows your original shortcuts and links to work.

3.2.7.Press the Compact button and take a short break.

3.2.8.When Jetcomp says, “Ready”, click on Exit.

3.2.9.Run the database and see what happens.

3.2.10.If Jetcomp does not work.

3.2.10.1.Check if the database has a long name with spaces; or the path has long names or spaces in it; or if the path or database name has nonalphanumerical characters, like ‘ or & or % or # or $, etc).

3.2.10.2.If so, try changing the database to a standard 8.3 name and/or moving it to a root folder like C:\ and then running Jetcomp.

3.2.10.3.If it still does not repair the database, repair it Without Jetcomp as described below especially if you are prompted to repair when attempting to open the database.

3.3.Without Jetcomp

3.3.1.First, make a copy of your database.

3.3.2.If you can’t open your database, open Access to a blank screen. If you can open your database, go ahead and open it.

3.3.2.1. Go to Tools, Database Utilities, Repair.

3.3.2.2. Find your bad database (if Access opened blank).

3.4.Note any messages about things that couldn’t be fix.

  1. Decompile and Recompile the Database.
  2. With the AccessReg tool.
  3. At in Resources, Downloads get AccessReg.Reg.
  4. Run AccessReg by double clicking on the downloaded file. This installs the AccessReg on that computer. You shouldn’t need to do it again.
  5. Find the naughty database in Windows Explorer.
  6. Right-click on the bad mdb file and you should see a Decompile option if AccessReg installed correctly.
  7. Select Decompile and follow the instructions.
  8. Without AccessReg.
  9. Go to Start/Run and type in the following with your path and db name.
  10. msaccess.exe /decompile c:\something\OrTheOther.mdb
  11. Recompile the database.
  12. After the database is decompiled, press the Ctrl + G keys.
  13. On the toolbar, go to Debug, Compile and Save All Modules.
  14. Close the database.
  15. Compact the database. (You might be surprised how much smaller it gets after dumping unused code.)
  16. Notes:
  17. If the database is split between a Front End and Back End mdb files, repair and decompile both databases as needed.
  18. If it ain’t broke, don’t fix it. You should decompile or repair only when there is problem.
  19. From Anna Walker on an AXP to A03 problem.

Just a quick note as a warning for those of you heading into the pastures of Access 2003:

My PC has Access 2003 installed, whilst the rest of the company, including our major software application still runs under Access 2002. I have been doing a fair amount of development using Access 2003 (there is no conversion nec for moving up), opening the 2002 mdbs (copies!) and using these. No problems most of the time except that every now and then Access 2003 falls over when opening one of the mdb's. Cannot get out of the program, except with the three-fingered salute, and task manager's End Task. Thereafter, on attempted opening (whether in 2002 or 2003), I am told that the database (mostly the frontend, but once the backend) is corrupted and needs repair, and it won't open. Repairing etc doesn't work, have tried all the tricks in Roger's Library, but eventually go back to a backup and re-construct the changes.

Eventually, I've tracked the cause down to this (I think):

If you leave the 2002 Mdb in an uncompiled state within an Access 2002 environment, with potential compile errors (e.g. one evening I had placed a "Start here tomorrow" comment in the code - without commenting it out!!!), then open it using Access 2003, it corrupts. So it must attempt a compile on opening, come unstuck, but because of the cross-version environment, it scribbles the VBA component.

I haven't investigated all the ramifications, I just keep really good backups and its quicker to go back to those. But haven't been able to find related problems on the Net, and thought it’s worth a warning to you at this stage. And my new Mantra is: Work carefully and carry a big backup!

  1. References.
  2. Every so often, some piece of code, like the Date Function doesn’t work. Also, the code will work on some computers but not others. It’s enough to drive you mad. The most likely cause is that on the machine where it won't work is that you have a missing reference.
  3. Open a code module and go to the Menu: Tools, References. If there are items shown as missing either install the missing component or uncheck the reference if it isn't required.
  4. You might want to compare the references on a working machine against the problem machine.
  5. It is also possible that you have extra references that are messing things up. If you compare two machines and find extra references on the troubled computer, try unchecking them and restarting the database.
  6. Memo and OLE Object fields in tables.
  7. An Access record can only be about 2,000 characters total. A memo field can be 64K and OLE Object, such as a bitmap, can be very large. The trick is that these fields aren’t actually stored in the table. Rather, they are linked from a hidden table. Therefore, memo and OLE objects are readily corrupted.
  8. If you are still having problems, check your Memo and OLE fields at the table level. You need to find which record(s) is causing the problem. In addition, if you have linked tables, you need to check each linked table until you find which one has the bad record.
  9. Check which fields in the table are memos and then scroll down through all of the records in that field. You will get an error when you find the bad field.
  10. If you have many records, you could try using queries to check a few records at a time until you narrow the field. Don’t be surprised if there is more than one bad record.
  11. Once you find the bad record(s), copy and paste the table's structure only.
  12. Next created an append query and moved all the records except for the bad one(s) to the new table.
  13. Then modify the append query to include only the bad record(s) but excluded the memo field.
  14. If this works you will be able to recover the table with only one field of a few records missing.
  15. You may need to redo the table relationships in the Relationship window.

  1. Everything works except for a form or report.
  2. You’ve repaired and decompiled
  3. That form or report is probably toast.
  4. You can try to export the defective form/report to another database and then reimport it. If you are lucky, the export/import will fix the error.
  5. Judy Wayne () suggests the following repair.
  6. Cut and paste the all code behind the form into a text file.
  7. Change the “Has Module” property in the form in question to No, thereby removing the module and deleting all the code.
  8. Save and close the form.
  9. Reopened the form and changed the “Has Module” property to Yes, thereby creating a new module
  10. Copy and paste the code from the text file into the new module.
  11. See if it works now.
  12. From Tom Arena: I have often encountered mysterious corruption while developing a DB. (Access 2k (sr-1), Win98 and Win2000) After much hair pulling, the offending issue is identified as a bug in Access when certain versions of a visual basic 6 DLL (VBE6.DLL) are on your computer. This is referenced in Microsoft Knowledge Base #:Q304548. To avoid it, when you import an object from another database, or just cut and paste it into your DB, you MUST then open up the compiler window on some code, (alt-f11) and compile the whole database BEFORE you save the database. If you do not, it will corrupt, or lose the code for the object imported, and worse, you can delete the offending object, but access will insist the name is still in use!
  13. If none of the above works, it’s time to import the form/report from a backup or recreate the form/report.
  14. Upgrading to fix corruption.
  15. When an Access 97 database gets corrupted to a point of no return, open A2K and upgrade the bad A97 dB to A2K. Then Repair, Compact and save backwards to A97.
  16. Thanks to Jim Barbieri.

  1. Getting Hexed.
  2. Added March 22, 2001 thanks to Andrea Conti:

I came up with the nasty "unrecognized database format" error this afternoon with an Access2000 project. The program appeared to be complaining about incorrect shutdown/permissions.

The usual tools, /decompile and compress-and-repair wouldn't run, delivered the same error message instead. I couldn't get anything to open in any mode, either the code or the forms, so I was looking at losing a couple hours' work.

After while, I dragged the .mdb file into a blank Visual Studio C++ project window - it does a nice job of opening binary files and displaying them in hex. While I was there I dragged in the previous known good mdb to see if any differences would be obvious.

Sure enough: the good mdb started with some "Jet ..." text. The "bad" mdb had a similar thing at 0x800.

I deleted the first 0x800 bytes of the "bad" mdb and saved it with a different name. Happily, that fixed it well enough so that it would load stuff and I could see the code and forms.

I copied the day's work to the previous good version - fortunately it was pretty localized, then tried compress-and-repair. It worked fine this time, so my "bad" copy is now officially "good."

Hope this helps someone else someday...

======

I did something similar. Using Hexpert32, I found that at address 0x5fa there was a difference; so I copied those 4 bytes from a good copy and it worked!!

  1. Repair using ODBC Sources.
  2. Added 12 November 2001 thanks to Roger Carlson
  3. Another option has been recently brought to my (Roger Carlson) attention. There is a repair facility in the ODBC Sources icon in the Control panel. This is reputed to be better than JetComp, though I cannot confirm that. You can get to it like this: BACK UP YOUR DATABASE FIRST!!
  4. From Windows get to the Control Panel. (Start->Settings->Control Panel).
  5. Now look for the ODBC Data Sources icon.
  6. In Windows XP it’s under Administrative Tools.
  7. Double-click it and then click the User DSN tab.
  8. Look for the line that says something like: "MS Access 97 Database" or "MS Access Database" this can vary depending on the OS, version of Access, or whether you have multiple versions installed.
  9. Double-click this line. Up will pop the ODBC Microsoft Access Setup
  10. The third button says "Repair..." Click it.
  11. You will get a standard Open dialog box. Navigate to the corrupt database and either double-click it or click it and push OK.

  1. Possibly saving the data in a corrupt database.
  2. When investigating a dreaded AOindex error message, I found the following on Google:

11.2.If that page goes missing, here’s the instructions in a nutshell

1. Open Microsoft Excel and create a new sheet (file).

2. Click on the "Data" drop down menu, select "Get external data" and click on "New database query".

3. Select "MS Access Database*".

4. On the "Select Database" dialog browse to where your "corrupted" database is located and open it (double click).

5. At this point you'll see a dialog box displaying all the tables in your database under the heading "Available tables and columns".

6. All you do is select your table and click the > arrow to move it over to the "Columns in your query" section.

7. Click "Next".

8. Click "Next" again.

9. In the "Sort by" just select the first field on your table (ID, Index, whatever).

10. On the next screen make sure the "Return data to Microsoft Excel" is selected and click "Finish".

11. In the "Where do you want to put the data" screen, make sure that "Existing worksheet" is selected and that " =$A$1" is showing.

12. Click Ok.

13. Now you'll see your data from that table as a excel worksheet. Save it and you're done. If you have more than one table in your Access database you'll have repeat steps 2 - 12 for every table you have.

14. After you've recovered and saved your table(s), open Microsoft Access and create a new blank database. Then:

15. Select the "File" drop down menu, select "Get External Data" and then select "Import..." .

16. Browse to where your saved Excel data (recovered table) is and import it. I' m assuming you know your way around Access so at this point the rest of the process is quite obvious.

  1. When a Database Seems Corrupt But Isn't.
  2. An A97 database was acting flakey so I created a new database and started importing all the objects into it. Sure enough a report would not import. Smugly I thought "Corrupt Report". Rebuilt the report but had the same problems. Tried importing into a new database again and some queries wouldn't come over. Decompiled. Recompiled. Compacted. Repaired. Jetcomp'ed. Nothing helped.
  3. In desperation, I took the original database to another PC and it worked just fine. Tried importing everything into a new database just to see what would not import. Everything imported just fine.
  4. Looks like a bad install can mimic a corrupt database. I checked references on both and even ran a virus checker on the troubled PC. The problem PC has been working like a charm for over a year and has nothing changed on it lately. Must just be one of those things.
  5. When all else fails, I hope that you have a good, recent backup.
  6. Recent backup not good enough? Some professional services can fix corrupt databases or at least recover some of the data. Expensive ($120 to $1200). One company that I have heard about is:

  1. How to stop databases from corrupting in the first place.
  2. Install the latest software patches.
  3. For the newer versions of Office, run Office Update every once in a while.
  4. Help, Check for Updates in almost any Office product.
  5. Office 97 Service Releases.
  6. Office 97 has two Service Releases that fix numerous bugs including some Y2K and security problems. It is very important that you have both of these SR’s installed.
  7. It is also very important that you install SR-1 and then SR-2 in order.
  8. How do you know? In Word, Excel, or Access, go to Help and select ‘About Microsoft Word’ (for example). You should see SR-1 or SR-2 within the window if either is installed and blank if neither is.
  9. You should order the SR’s from Microsoft on CD as SR-2 is over 25MB in size. If you order the CD, you get both SR-1 and SR-2 together. Go to
  10. If you have plenty of time, you can download both SRs.
  11. To confuse matters, there are special Service Packs for Access that includes an updated Jet engine.
  12. If you have SR-2 installed (see above), you can install SP-3 located at
  13. For the latest, greatest updated version of Microsoft Jet 4.0 go to
  14. If you are using a newer version than Access 97, go to the Office Update page and install the latest and greatest.

14.2.The database is used by more than one person on a network.