We help IT Professionals succeed at work.

How to fix error in database: microsoft office access has detected corruption in this file

datasolutionz
datasolutionz used Ask the Experts™
on
I am in the process of developing an Access 2003 application, and almost done. While I was fixing some errors with controls on one of the reports, seems like the database corrupted. Now, I keep getting "microsoft office access has detected corruption in this file....etc". It instructs me to Compact & Repair, but when I get the same error message when I try that. If I open a blank database & try to import objects from the corrupted database, I cannot access/view the objects of the corrupted file from the blank database. Unfortunately, I dont have a backup of the file, I can try to restore to an earlier storage point (using System Restore), but will lose considerable work. Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
To clarify, when i try to import the objects from the blank database, I get the same error message of " Microsoft Office Access has detected a corruption in this file"

Commented:
you must open the database with parametre /compile
Example:

c:\ProgramFiles|Office10\Msaccess.exe c:\Database1.accdb /compile

This will repair your database

Author

Commented:
I also tried System Restore which completed successfully. But the weird part is that the corrupt version of the file is still there & it shows the last modified date as AFTER the System Restore point, ie, the file did not get restored t an earlier version. I thought that in Windows System Restore, you restore all settings, file & data to an earlier restore point. Moreover, the backup & other test files that I had created (i.e. after the selected restore point) in trying to fix up the corruption, also show up. I thought I would get file versions going back to the point of restoration. I am really concerned now.

Author

Commented:
Dignos, thanks for your help, can you be a little more specific? I dont have the Office10 folder under the C:\Program Files. folder. I do have the OFFICE11 folder under "C:\Program Files\Microsoft Office\OFFICE11. The name of my file is Marty Gomez.mdb & its location is C:\Documents and Settings\bkhan\Desktop\joel. Hence, the command I typed in Start-Run was:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"  "C:\Documents and Settings\bkhan\Desktop\joel\Marty Gomez.mdb" /compile

I got the message "The command line you used to start Microsoft Office Access contains an option that Microsoft Office Access does not recognise. ". Please help!>
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
There is no such command line switch as /compile, and I think that dignos is referring to the DEcompile switch. This can work to deal with corruption, but it's only part of the battle.

The steps I take are as such:

MAKE A BACKUP! This is critical

1) Open the database and Compact it. Click Tools - Database Utilities - Compact.
2) Compile the app. From the VBA Editor click Debug - Compile. Fix any errors, and contine compiling until the menuitem is greyed out.
3) Compact again
4) Now Decompile. Build a desktop shortcut that looks like this and launch it:

"full path to msaccess.exe" "full path to your db" /decompile

5) Now again Compact
6) Compile
7) Compact

That should do it; if not, then your app is hopelessly corrupt and you'll have to recreate from scratch or hire a recovery service.

You can also try simply moving everything to a new, blank database, but if you have corrupt objects often those will import over and begin the corruption process all over again.

As far as restoring - my understanding of that is that it ONLY restores program files, not user documents, so your situation would be expected. Windows doesn't keep full versioning copies of all files, at least to my understanding.
Chris BRetired

Commented:
A simple method which has worked for me, is to open the 2003 mdb with Access 2007. Convert it to 2007 format, then convert it back to 2003 format. It seems that 2007 has better methods of dealing with corruption. Try with a copy of your db of course. For next time, a critical part of developing with Access is to take a backup every time you change something significant, and always daily in any event.

Chris B

Commented:
I have writed a mistake,  the commnad line was /decompile

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"  "C:\Documents and Settings\bkhan\Desktop\joel\Marty Gomez.mdb" /decompile
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
<a critical part of developing with Access is to take a backup every time you change something significant, and always daily in any event.>

Amen to that! Backup early and often, as I like to say.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Generally, when you get *that* specific error message when the subsequent result your getting ... your mdb is toast.  Mostly likely your only hope now is a repair service ... and I would start with this one that several people have had success with:

http://www.everythingaccess.com/accessdatabaserepair.htm 

More:

http://www.accessdatabaserepair.com/

http://www.exefind.com/advanced-access-repair-P1475.html

http://accesstools.narod.ru 

I generally takes less than 5 seconds to back up any MDB in a given folder.  Control C then Control V ... seriously. You should be doing this frequently during your design work.

mx

Author

Commented:
@LMSConsulting: When I try Database Utilities --> Compact & Repair, I get the same error message, its like a loop. The option I have is Compact & Repair, I dont have a just "Compact" option. Hence, I dont think I can proceed further with your suggestion.

@Burrcm: As far as my knowledge & experience is concerned, you can convert 2003 format to 2007, but not backwards, i.e. 2007 cannot be converted back to 2003. Please explain if I am missing something.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I forgot one other thing.  You can try JetComp and you might get lucky, however a similar Q last week did not get good results ... but it's easy to try:

http://support.microsoft.com/kb/295334
One note:  Be sure you specify a *different* mdb name for the Destination text box.

JetComp is sort of Compact and Repair on steroids ... sometimes works when C&R (especially the Repair part of C&R) fails.  

mx
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try JetComp, as suggested by mx. This can sometimes work if you have issues opening the database.

Otherwise:

Can you import everything into a new database? You may not be able to.

You can _try_ the SaveAsText and LoadFromText methods. These can sometimes work, but given the issue you've got I'm not sure it will.

http://www.granite.ab.ca/access/corruption/corruptobjects.htm

Toward the end of that page, see the link: http://www.granite.ab.ca/access/corruption/retrievedata.htm

Commented:
try this
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"  "C:\Documents and Settings\bkhan\Desktop\joel\Marty Gomez.mdb" /decompile
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Decompile has already been mentioned above.  And not likely to work because the MDB cannot even be opened.

mx
Retired
Commented:
<<@Burrcm: As far as my knowledge & experience is concerned, you can convert 2003 format to 2007, but not backwards, i.e. 2007 cannot be converted back to 2003. Please explain if I am missing something.>>

Of course you can. File - Save As - Access 2002-2003 database. (and other options).

Chris B
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
datasolutionz:

You stated:

"I am in the process of developing an Access 2003 application,"

 I'm a bit confused.  What does this have to do with A2007?

mx
Chris BRetired

Commented:
Hi mx. As I mentioned above, converting a damaged 2003 db to 2007 and back to 2003 really can work. I have done this a number of times successfully, where other options have failed - even when A2003 could not read the file for import into a new db.

Chris B
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"converting a damaged 2003 db to 2007 '
And how do you do that in this case?

mx
Chris BRetired

Commented:
Fire up 2007 and file open.....

Chris B

Author

Commented:
Yes, I agree with burrcm, I opened it in Access 2007 but retianed the 2003 format. Worked wonderfully! I then did a Compact & Repair (which by the way is a very obscure option in Access 2007) & then opened it back on another laptop in Access 2003.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well that's amazing then.

datasolutionz:  Did you ever try JetComp.  If not, I wish you would ... just for the record.

mx

Author

Commented:
Yes, i did get JetComp & unfortunately, it did not work. I got the same error message I was getting when I tried it.
Chris BRetired

Commented:
Hey, I amazed someone amazing. That's gotta be good. Check out mx's profile. At my level, that would take several lifetimes to match. Sometimes even the silly stuff works.

Chris B
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well, I guess we learned a new trick here.

datasolutionz:  Do you happened to have a copy of the corrupted version I can play with ... that you can zip up and upload ?

mx

Author

Commented:
MX: there you go.
Marty-Gomez.zip
Most Valuable Expert 2012
Top Expert 2013

Commented:
Just another comment about using A2007 compact and repair on an A2003 database ...

I've had accidental (but pretty consistent) luck with this significantly shrinking the size of A2003 databases that do not get any smaller by simply compacting in A2003.