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.
1. …..FIRST MAKE A BACKUP COPY OF THE DATABASE. ‘nuff said.
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.
184.108.40.206. Replace the corrupt object from a recent backup (you do have
220.127.116.11. A last chance procedure from David Hare-Scott, Syncretic
Solutions Pty Ltd, syncreticsolutions@OPTUSHOME.COM.AU added on
May 30, 2002.
18.104.22.168.1. Move code in corrupt form to a safe place like a Word file.
22.214.171.124.2. Remove code module from form.
126.96.36.199.3. Import again.
188.8.131.52.4. Restore module.
184.108.40.206.5. Restore code.
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.
220.127.116.11. Microsoft recommends using it instead of the built-in Access
compact and repair utilities.
18.104.22.168. You can get the Jetcomp utility at:
22.214.171.124.1. Note: The latest version of JetComp (dates 8/11/99) works
for Access 2000 databases and all previous versions.
126.96.36.199.2. 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.
188.8.131.52.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.
184.108.40.206. 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).
220.127.116.11. 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.
18.104.22.168. 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.
22.214.171.124. Go to Tools, Database Utilities, Repair.
126.96.36.199. Find your bad database (if Access opened blank).
3.4. Note any messages about things that couldn’t be fix.
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 http://www.mvps.org/access/ 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.
188.8.131.52. 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.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!
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.
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 (firstname.lastname@example.org) 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 (email@example.com), this has worked
extremely well after trying everything else.
9. Added March 22, 2001 thanks to Andrea Conti <Aconti@CalicoCorners.com>:
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 http://www.rogersaccesslibrary.com
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
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.
184.108.40.206. 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.
220.127.116.11. It is also very important that you install SR-1 and then SR-2 in
18.104.22.168. 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.
22.214.171.124. 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 http://support.microsoft.com/support/kb/articles/Q192/8/74.asp
126.96.36.199. 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.
188.8.131.52. If you have SR-2 installed (see above), you can install SP-3
located at http://support.microsoft.com/support/kb/articles/Q172/7/33.asp
184.108.40.206. 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
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
13.3.5. How to split a database.
220.127.116.11. FIRST MAKE A BACKUP COPY. ‘nuff said.
18.104.22.168. Go to Tools, Add-ins and select Database Splitter.
22.214.171.124. Follow the directions.
126.96.36.199. If it doesn’t work, you probably need to do a Setup of Microsoft
Office and install all the Access components.
188.8.131.52. 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.
184.108.40.206. Other warnings about split databases.
220.127.116.11.1. 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!
18.104.22.168.2. 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:
22.214.171.124.3. 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.
126.96.36.199.4. Also, I've heard, but not experienced, that Query-by-Form
can stop working properly.
13.4.1. Make sure that each user has full rights and privileges to the network
folder and files containing the database.
188.8.131.52. If the network folder is read only, can’t add records.
184.108.40.206. 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.
220.127.116.11. 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.
18.104.22.168. Novell networks also have a unique problem.
22.214.171.124.1. 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.
126.96.36.199.2. Have your network people read and heed the Document ID
2935504 from Novell at :
188.8.131.52. Bob Goethe found this information about Opportunistic Locking
(oplocks) on the Network File Server.
184.108.40.206.1. 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.
220.127.116.11.2. Check out the following web sites.
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.
18.104.22.168. About once a month, do a complete Scandisk including checking
for bad sectors. Do it more often on a suspect computer.
22.214.171.124. 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.
126.96.36.199. 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
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.
188.8.131.52. 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: http://www.rogersaccesslibrary.com/misc/misc.htm
14.6. Dev Ashish’s excellent site: http://www.mvps.org/access/
14.7. Tony Toews Corrupt Microsoft Access MDBs FAQ
15. If all else fails, email Jerry Whittle at firstname.lastname@example.org.