Fixing a Corrupt Database

Document Sample
Fixing a Corrupt Database Powered By Docstoc
					                      Fixing a Corrupt or Problem Access Database v3.1
     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 wrote the procedures below based on Access 97. There are differences with other versions of Access.

2. Can’t even open the database.
    2.1. Can you open any other databases?
           2.1.1. If not, something is wrong with your computer and not the database.
           2.1.2. If Access opens but the database causes an error, it could be the database.
    2.2. 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.
    2.3. 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.
    2.4. If that doesn’t work, repair the database as described below.
    2.5. 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.
           2.5.1. You will need to go to Tools, Options, View Tab, and make sure Hidden
                Objects and System Objects are checked to see everything.
           2.5.2. Import only a few items at a time.
           2.5.3. If an item won’t import, it is probably corrupt.
       Replace the corrupt object from a recent backup (you do have
                     backups?)     OR
        A last chance procedure from David Hare-Scott, Syncretic
                     Solutions Pty Ltd, syncreticsolutions@OPTUSHOME.COM.AU added on
                     May 30, 2002.
                   Move code in corrupt form to a safe place like a Word file.
                   Remove code module from form.
                   Import again.
                   Restore module.
                   Restore code.

06/17/12                                                                                                             1
3. Repair the Database.
    3.1. Get everyone off the database.
    3.2. With JetComp.
           3.2.1. The Jetcomp utility is a stand-alone program that does a much better job
                of repairing and compacting Access databases.
       Microsoft recommends using it instead of the built-in Access
                     compact and repair utilities.
          You can get the Jetcomp utility at:
             Note: The latest version of JetComp (dates 8/11/99) works
                           for Access 2000 databases and all previous versions.
              The knowledgeable David Hare-Scott of Syncretic
                           Solutions Pty Ltd (syncreticsolutions@OPTUSHOME.COM.AU)
                           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.

              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.

06/17/12                                                                                                    2
           3.2.10. If Jetcomp does not work.
     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
                     non-alphanumerical characters, like ‘ or & or % or # or $, etc).
     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.
     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.
        Go to Tools, Database Utilities, Repair.
        Find your bad database (if Access opened blank).
    3.4. Note any messages about things that couldn’t be fix.

06/17/12                                                                                         3
4. Decompile and Recompile the Database.
                Note: for a lengthy discussion of decompiling and compacting, go to:

    4.1.     With the AccessReg tool.
           4.1.1. At in Resources, Downloads get AccessReg.Reg.
           4.1.2. Run AccessReg by double clicking on the downloaded file. This installs
                the AccessReg on that computer. You shouldn’t need to do it again.
           4.1.3. Find the naughty database in Windows Explorer.
           4.1.4. Right-click on the bad mdb file and you should see a Decompile option if
                AccessReg installed correctly.
           4.1.5. Select Decompile and follow the instructions.
    4.2. Without AccessReg.
           4.2.1. Go to Start/Run and type in the following with your path and db name.
        msaccess.exe /decompile c:\something\OrTheOther.mdb
    4.3. Recompile the database.
           4.3.1. After the database is decompiled, press the Ctrl + G keys.
           4.3.2. On the toolbar, go to Debug, Compile and Save All Modules.
           4.3.3. Close the database.
           4.3.4. Compact the database. (You might be surprised how much smaller it gets
                after dumping unused code.)
    4.4.     Notes:
           4.4.1. If the database is split between a Front End and Back End mdb files, repair
                and decompile both databases as needed.
           4.4.2. If it ain’t broke, don’t fix it. You should decompile or repair only when
                there is problem.
           4.4.3. From Tom Arena Tom_Arena@RCAB.ORG: 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!

06/17/12                                                                                            4
5. 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.
    5.1. The most likely cause is that on the machine where it won't work is that you have
         a missing reference.
           5.1.1. 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.
           5.1.2. You might want to compare the references on a working machine against
                the problem machine.
    5.2. 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.
    6.1. 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.
    6.2. 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.
           6.2.1. 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
           6.2.2. 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.
    6.3. Once you find the bad record(s), copy and paste the table's structure only.
           6.3.1. Next created an append query and moved all the records except for the bad
                one(s) to the new table.
           6.3.2. Then modify the append query to include only the bad record(s) but
                excluded the memo field.
           6.3.3. If this works you will be able to recover the table with only one field of a
                few records missing.
           6.3.4. You may need to redo the table relationships in the Relationship window.

06/17/12                                                                                         5
7. Everything works except for a form or report and you’ve repaired and decompiled.
    7.1. That form or report is probably toast.
    7.2. You can try to export the defective form/report to another database and then
         re-import it. If you are lucky, the export/import will fix the error.
    7.3. Judy Wayne ( suggests the following repair.
           7.3.1. Cut and paste the all code behind the form into a text file.
           7.3.2. Change the “Has Module” property in the form in question to No, thereby
                removing the module and deleting all the code.
           7.3.3. Save and close the form.
           7.3.4. Reopened the form and changed the “Has Module” property to Yes,
                thereby creating a new module
           7.3.5. Copy and paste the code from the text file into the new module.
           7.3.6. See if it works now.
    7.4. From Tom Arena <Tom_Arena@RCAB.ORG> 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!
    7.5. If none of the above works, it’s time to import the form/report from a backup or
         recreate the form/report.
8. I have recently hear that when a Access '97 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. According to Jim Barbieri (, this has worked
   extremely well after trying everything else.

06/17/12                                                                                    6
9. Added March 22, 2001 thanks to Andrea Conti <>:
      I found this on a newgroup:
      I came up with the nasty "unrecognized database format" error this afternoon with an
      Access2000 project. The program appeared to be complaining about incorrect
        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!!

10.   Added 12 November 2001 thanks to Roger Carlson
      10.1. 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!!
      10.2. From Windows get to the Control Panel. (Start->Settings->Control Panel).
      10.3. Now look for the ODBC Data Sources icon.
      10.4. Double-click it and then click the User DSN tab.
      10.5. 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
      10.6. Double-click this line. Up will pop the ODBC Microsoft Access Setup
      10.7. The third button says "Repair..." Click it.
      10.8. You will get a standard Open dialog box. Navigate to the corrupt database and either double-click
            it or click it and push OK.

11. When a Database Seems Corrupt But Isn't (added August 14, 2002 by Jerry Whittle)
      11.1.       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/Database". 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.
      11.2.       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.
      11.3.        Looks like a bad install can mimick 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.

06/17/12                                                                                                    7
12. When all else fails, I hope that you have a good, recent backup.
    12.1.     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:
13. How to stop databases from corrupting in the first place.
    13.1.         Install the latest software patches.
           13.1.1. Office 97 Service Releases.
     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.
     It is also very important that you install SR-1 and then SR-2 in
     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.
     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
        If you have plenty of time, you can download both SRs.
           13.1.2. To confuse matters, there are special Service Packs for Access that
                includes an updated Jet engine.
     If you have SR-2 installed (see above), you can install SP-3
                     located at
       For the latest, greatest updated version of Microsoft Jet 4.0 go to
    13.2.         The database is used by more than one person on a network.
           13.2.1. Go to Tools, Options and select the Advanced tab.
           13.2.2. Change Default Record Locking to Edited Record.
           13.2.3. It’s also a good time and place to set Default Open Mode for Databases to
    13.3.         Split the database
           13.3.1. Usually the things that corrupt are the forms and reports. Splitting the
                database between a front end (FE) and a back end (BE) can save your
                valuable data.

06/17/12                                                                                              8
           13.3.2. It also allows you to make changes to a form or report in a copy of the FE
                without disturbing someone using the production FE.
           13.3.3. If you have multiple users on a database, putting the BE on a network
                drive and a copy of the FE on each users’ hard drive increases performance
                and decreases the changes of a single user crashing the database.
           13.3.4. It does cause a little more work in modifying table structure and keeping
                tables linked.
           13.3.5. How to split a database.
             FIRST MAKE A BACKUP COPY. ‘nuff said.
       Go to Tools, Add-ins and select Database Splitter.
       Follow the directions.
     If it doesn’t work, you probably need to do a Setup of Microsoft
                     Office and install all the Access components.
     If after you split the database and have problems linking to tables,
                     use the Linked Table Manager to refresh the links. This can happen if
                     you move the BE database file to another location.
       Other warnings about split databases.
            The Seek function won’t work directly with linked table.
                          You’ll need to open a recordset to use Seek. Congratulations for
                          getting that far into Visual Basic!
           If you use the Switchboard Manager add-in to create a
                          Switchboard form, the Switchboard Items table must be located in
                          the FE database file. Delete the link and import the table. For
                          more info, check out:
             If you use the trick of putting a bitmap file in the same
                          folder with the same name as the FE to have a custom opening
                          splash screen, you may need to rename both the FE and bitmap
                          file to an old MS-DOS 8.3 name to get it to work.
            Also, I've heard, but not experienced, that Query-by-Form
                          can stop working properly.
    13.4.         Networks
           13.4.1. Make sure that each user has full rights and privileges to the network
                folder and files containing the database.
       If the network folder is read only, can’t add records.
     If you can’t create new files, Access can’t create the .ldb locking
                     file needed to keep track of who is using the database.

06/17/12                                                                                        9
     If you can’t delete files, Access can’t delete the .ldb locking file
                     after everyone leaves the database. This confuses Access the next time
                     someone opens the database.
        Novell networks also have a unique problem.
             If you notice that a query, form, or report isn’t returning all
                          the records it should be, the number of record locks could be set
                          too low. This mostly happens when you are working with
                          thousands of records. It also happens when importing or exporting
                          data to another program.
           Have your network people read and heed the Document ID
                          2935504 from Novell at :
     Bob Goethe found this information about Opportunistic Locking
                     (oplocks) on the Network File Server.
               Microsoft has discovered an issue where opportunistic locking can
                            increase the risk of Jet database corruption when the file is shared by two or
                            more clients on a network file server. This issue applies to Microsoft Windows
                            NT 4.0, Microsoft Windows 2000, and Novell file servers that support
                            opportunistic locking. This issue also applies to clients that are running
                            Microsoft Windows NT 4.0, Microsoft Windows 2000, or Microsoft Windows
                            XP, and that connect to a file server that supports opportunistic locking.

                 Check out the following web sites.

    13.5.         Hardware
           13.5.1. Access databases are usually kept in one or two large files. If you have a
                defective hard drive, they can be corrupted quite easily.
     About once a month, do a complete Scandisk including checking
                     for bad sectors. Do it more often on a suspect computer.
     If while doing a Scandisk, you start finding physical defects on the
                     hard drive, time for a complete backup and new hard drive.
           13.5.2. If it’s on a network, a bad server, hub, or router can cause grief.
           13.5.3. If only one computer is having problems, the Network Interface Card
                (NIC) or bad wiring to the computer are common suspects. Access really
                works the network harder than something like Word does. If you have any
                doubts about the NIC or wiring, replace them.
           13.5.4. Memory. To work at all, Access97 needs 32mb of memory. 64mb is even
                better and so is 96mb. Anything over 128mb is gravy. 16mb sucks.
           13.5.5. CPU. 100MHz is the bare minimum. The higher the better.
           13.5.6. Hard Drive. You need at least 200mb open to run one Access database. If
                you run more than one database at a time, add 100mb per open database.

06/17/12                                                                                                             10
            Using Jetcomp to compact the database really helps here.
           13.5.7. According to Bob Goethe [Bob@MAMMOBASE.COM] power-saving
                options on the individual computers might corrupt a database that is on a
                server. Below is an extract about a corruption problem his users were
                experiencing frequently.
                  Computers that have power-saving options and are configured to "go to sleep". It turns out that a bunch
                  of the people had PCs that went to sleep...including powering down their network interface cards (NIC).
                  Well!! Having the NICs go to sleep is as good as having multiple, overlapping power failures, as far as
                  the MDB files are concerned. They were getting hammered. No WONDER they corrupted. We have
                  configured all the workstations so that the monitors can go to sleep, but the CPUs all stay powered up. I
                  have even tried to discourage the customer from powering down their hard drives. Paying another nickle
                  a day for electricity is a small price to pay. At this point, I want the security of suspenders AND a belt.

           13.5.8. Bob also thinks that AutoDisconnect by the network might be a problem.
             As for AutoDisconnect, please see this MS Knowledge Base article:

14. Other resources:
    14.1.      ACC: How to Troubleshoot/Repair Damaged Jet 3.0 and Prior Databases
           14.1.1. Microsoft Knowledge Base Article Q109953
    14.2.            ACC97: How to Repair a Damaged Jet 3.5 Database
           14.2.1. Microsoft Knowledge Base Article Q279334
    14.3.            ACC2000: How to Repair a Damaged Jet 4 Database
           14.3.1. Microsoft Knowledge Base Article Q209137
    14.4.            Latest Jetcomp Info

    14.5.            Good Stuff:
    14.6.            Dev Ashish’s excellent site:
    14.7.            Tony Toews Corrupt Microsoft Access MDBs FAQ
15. If all else fails, email Jerry Whittle at

06/17/12                                                                                                                  11

Shared By:
Lingjuan Ma Lingjuan Ma