Fix Corrupt Access Database - Roger's Access Library



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

2. Can’t even open the database. 2

3. Repair the Database. 3

4. Decompile and Recompile the Database. 5

5. References. 7

6. Memo and OLE Object fields in tables. 7

7. Everything works except for a form or report. 8

8. Upgrading to fix corruption. 8

9. Getting Hexed. 9

10. Repair using ODBC Sources. 9

11. Possibly saving the data in a corrupt database. 10

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

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

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

14.1. Install the latest software patches. 12

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

14.3. Split the database 13

14.4. Networks 14

14.5. Hardware 15

14.6. Turn off Compact on Close 17

14.7. Do NOT put Access databases on compressed drives. 17

15. Other resources: 17

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

2. Can’t even open the database.

1. Can you open any other databases?

1. If not, something is wrong with your computer and not the database.

2. If Access opens but the database causes an error, it could be the database.

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.

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.

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

5. If that doesn’t work, repair the database as described below.

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

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. Import only a few items at a time.

3. If an item won’t import, it is probably corrupt.

1. Replace the corrupt object from a recent backup (you do have backups?) OR

2. A last chance procedure from David Hare-Scott.

1. Move code in corrupt form to a safe place like a Word file.

2. Remove code module from form.

3. Import again.

4. Restore module.

5. Restore code.

3. Repair the Database.

1. Get everyone off the database.

2. With JetComp.

1. The Jetcomp utility is a stand-alone program that does a much better job of repairing and compacting Access databases.

1. Microsoft recommends using it instead of the built-in Access compact and repair utilities.

2. You can get the Jetcomp utility at:





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

2. The knowledgeable David Hare-Scott of Syncretic Solutions Pty Ltd (syncreticsolutions@.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.

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)

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

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

4. Run JETCOMP.exe.

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

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.

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

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

9. Run the database and see what happens.

10. If Jetcomp does not work.

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 non-alphanumerical characters, like ‘ or & or % or # or $, etc).

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. 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. Without Jetcomp

1. First, make a copy of your database.

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.

1. Go to Tools, Database Utilities, Repair.

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

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

4. Decompile and Recompile the Database.

1. With the AccessReg tool.

1. At in Resources, Downloads get AccessReg.Reg.

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.

3. Find the naughty database in Windows Explorer.

4. Right-click on the bad mdb file and you should see a Decompile option if AccessReg installed correctly.

5. Select Decompile and follow the instructions.

2. Without AccessReg.

1. Go to Start/Run and type in the following with your path and db name.

1. msaccess.exe /decompile c:\something\OrTheOther.mdb

3. Recompile the database.

1. After the database is decompiled, press the Ctrl + G keys.

2. On the toolbar, go to Debug, Compile and Save All Modules.

3. Close the database.

4. Compact the database. (You might be surprised how much smaller it gets after dumping unused code.)

4. Notes:

1. If the database is split between a Front End and Back End mdb files, repair and decompile both databases as needed.

2. If it ain’t broke, don’t fix it. You should decompile or repair only when there is problem.

3. 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!

5. References.

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

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.

2. You might want to compare the references on a working machine against the problem machine.

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.

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.

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.

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

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.

3. Once you find the bad record(s), copy and paste the table's structure only.

1. Next created an append query and moved all the records except for the bad one(s) to the new table.

2. Then modify the append query to include only the bad record(s) but excluded the memo field.

3. If this works you will be able to recover the table with only one field of a few records missing.

4. You may need to redo the table relationships in the Relationship window.

7. Everything works except for a form or report.

1. You’ve repaired and decompiled

2. That form or report is probably toast.

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

4. Judy Wayne (jmwayne@) suggests the following repair.

1. Cut and paste the all code behind the form into a text file.

2. Change the “Has Module” property in the form in question to No, thereby removing the module and deleting all the code.

3. Save and close the form.

4. Reopened the form and changed the “Has Module” property to Yes, thereby creating a new module

5. Copy and paste the code from the text file into the new module.

6. See if it works now.

5. 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!

6. If none of the above works, it’s time to import the form/report from a backup or recreate the form/report.

8. Upgrading to fix corruption.

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

2. Thanks to Jim Barbieri.

9. Getting Hexed.

1. 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!!

10. Repair using ODBC Sources.

1. Added 12 November 2001 thanks to Roger Carlson

2. 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!!

3. From Windows get to the Control Panel. (Start->Settings->Control Panel).

4. Now look for the ODBC Data Sources icon.

1. In Windows XP it’s under Administrative Tools.

5. Double-click it and then click the User DSN tab.

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

7. Double-click this line. Up will pop the ODBC Microsoft Access Setup

8. The third button says "Repair..." Click it.

9. 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. Possibly saving the data in a corrupt database.

1. When investigating a dreaded AOindex error message, I found the following on Google:



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.

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

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

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.

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

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

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:

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

1. Install the latest software patches.

1. For the newer versions of Office, run Office Update every once in a while.

1. Help, Check for Updates in almost any Office product.

2. Office 97 Service Releases.

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

2. It is also very important that you install SR-1 and then SR-2 in order.

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

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

5. If you have plenty of time, you can download both SRs.

1.

2.

3. To confuse matters, there are special Service Packs for Access that includes an updated Jet engine.

1. If you have SR-2 installed (see above), you can install SP-3 located at

2. For the latest, greatest updated version of Microsoft Jet 4.0 go to

4. If you are using a newer version than Access 97, go to the Office Update page and install the latest and greatest.



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

1. Go to Tools, Options and select the Advanced tab.

2. Change Default Record Locking to Edited Record.

3. It’s also a good time and place to set Default Open Mode for Databases to Shared.

3. Split the database

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.

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.

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.

4. It does cause a little more work in modifying table structure and keeping tables linked.

5. How to split a database.

1. FIRST MAKE A BACKUP COPY. ‘nuff said.

2. Go to Tools, Add-ins and select Database Splitter.

3. Follow the directions.

4. If it doesn’t work, you probably need to do a Setup of Microsoft Office and install all the Access components.

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

6. Other warnings about split databases.

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!

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:

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.

4. Also, I've heard, but not experienced, that Query-by-Form can stop working properly.

4. Networks

1. Make sure that each user has full rights and privileges to the network folder and files containing the database.

1. If the network folder is read only, can’t add records.

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

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

4. Novell networks also have a unique problem.

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.

2. Read and heed the Document 2935504 from Novell at :





5. Bob Goethe found this information about Opportunistic Locking (oplocks) on the Network File Server.

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.

2. Check out the following web site.

1.

5. Hardware

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.

1. About once a month, do a complete Scandisk including checking for bad sectors. Do it more often on a suspect computer.

2. If while doing a Scandisk, you start finding physical defects on the hard drive, time for a complete backup and new hard drive.

2. If it’s on a network, a bad server, hub, or router can cause grief.

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.

4. Speaking of network cards, here's something that Bob Goethe found in June 2006:

I just discovered what was to me a previously unknown source of database corruption on one of our customer networks.

Symptoms: Our database performance slowed down some weeks ago, but the customer didn't tell us. It was not just Access performance, though. File- copy operations that would normally take 15 seconds were taking 20 minutes.

We heard about this when eventually this degraded network performance resulted in database corruption

Solution: I watched the IS guy go in and change the Link Speed setting for the network card (NIC) to 100 mbps/Full Duplex, and voila! there was an immediate transformation of file-copy time and of database responsiveness.

In researching on Google, I see that if there is a mismatch between NIC settings and the settings of the network switch, one or more components will default back to half duplex performance, with a consequent rise in packet collisions (i.e. a slowing of apparent performance, and a rise in data errors). That is, it will eventually corrupt your database.

Further research indicates that because of file access characteristics, MS Access databases can generate a lot of lost packets at the switch even when both sides are set to what is usually a good setting (auto on the NICs, auto on the switch).

Increase both performance and database stability by eliminating the "Auto- Detect" settings, and set all devices to 100 mbps/Full Duplex.

Setting Link Speed and Duplex of a NIC: Control Panel >> Network Connections; Right click "Local Area connection" and choose Properties >> hit "Configure" button next to description of your NIC >> Advanced tab >> Link Speed and Duplex >> Change value to 100 mbps/Full Duplex.

Naturally, changing a single NIC on a network is insufficient. All devices must synchronize properly with each other.

Bob Goethe

5. Memory. To work at all, Access97 needs 32mb of memory. 64mb is even better and so is 128mb. Anything over 256mb is gravy. 16mb sucks.

1. Newer versions such as Office 2003 Professional need at least 128mb (512 or more is better).

6. CPU. 100MHz is the bare minimum for A97. The higher the better. For Access 2003 a 233-MHz or faster processor (Pentium III recommended).

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

1. Using Jetcomp to compact the database really helps here.

8. According to Bob Goethe [Bob@] 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.

9. Bob also thinks that AutoDisconnect by the network might be a problem.

1. As for AutoDisconnect, see this MS Knowledge Base article:



10. Bob also suggested that overclocked CPUs can cause mysterious Windows problems and therefore could be troublesome with Access.

1. For the most part computers from major manufacturers such as Dell and Gateway are not overclocked.

2. However some ‘mom and pop’ computer stores have been known to overclock CPUs. Quote from Raymond Chen of Microsoft: “Most often those who are "unintentionally overclocking" bought their computers from a whitebox maker who bought cheap Pentium chips and then boosted the clock speed to make them look like they were more expensive Pentium chips.”

3. Also some users overclock their own computers.

4. If you have some Access issues plus Windows problems, maybe just maybe your computer is overclocked. Take it to a good computer hardware person and they should be able to tell.

11. Similar to the overclocking above if the computer is overheating, that can cause errors. Make sure all fans, especially if there is one on the cpu, are clean and working. Maybe open up the case every so often and blow out the dust bunnies with some ‘canned air’.

6. Turn off Compact on Close

1. Tools, Options, General tab. Uncheck Compact on Close

1. While there also turn of Name AutoCorrect. You'll be glad that you did.

2. I personally experienced corruptions problems caused by a bad install of Office and Compact on Close.

3. There are reports that computers with the NTFS file system can have problems using Compact on Close.

1. Symptoms include a “The Microsoft Jet database engine cannot open the file” message.



7. Do NOT put Access databases on compressed drives.

1. According to MS, placing a Microsoft Jet database on a compressed drive may cause write-through delays that can cause Jet database corruption.



15. Other resources:

1. ACC: How to Troubleshoot/Repair Damaged Jet 3.0 and Prior Databases

1.

2. ACC97: How to Repair a Damaged Jet 3.5 Database

1.

3. ACC2000: How to Repair a Damaged Jet 4 Database

1.

4. Latest Jetcomp Info

1.

2.

5. Good Stuff:

6. Dev Ashish’s excellent site:

7. Tony Toews Corrupt Microsoft Access MDBs FAQ

1.

8. This company claims to be able to fix corrupt databases quickly and cheaply. They also claim to be able to convert MDEs back to MDBs.

1.

16. If all else fails, email Jerry Whittle at whittlej@.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download