Repairing database corruption
I've been developing this Access 2007/Sql Server application for 3 years now. The .ACCDB file for the program is 90 meg. Maybe once a month access crashes while I am in the middle of doing something. I know all too well that although I can re-open it and continue working, but the next time I publish a .ACCDE, the user will get an error saying something about an expression that could not be found.
I have the cure for this. Sometimes I just need to repair/compact the database. Then re-publish the .ACCDE. Other times this is not sufficient and I need to decompile the database also.
So I was wondering..... Is there a preferred sequence of doing this? Should I repair and compact first before decompiling? Or should I decompile first, and then repair and compact?
Ian
I have the cure for this. Sometimes I just need to repair/compact the database. Then re-publish the .ACCDE. Other times this is not sufficient and I need to decompile the database also.
So I was wondering..... Is there a preferred sequence of doing this? Should I repair and compact first before decompiling? Or should I decompile first, and then repair and compact?
Ian
ASKER
It's not happening regularly. Only once a month on average. Importing into a new database is something i've done in the past, but it doesn't stop the problem recurring. I can't be 100% sure, but I've formed that opinion that the slowness of my pc is causing problems. Usually the crash happens when I save whichever form or report that I am currently working on.
Like I said, sometimes I only need to repair and compact, but my question is based around the situation when reparing and compacting is not enough.....
When I need to decompile also, should the decompilation be done before or after the repair and compact?
Like I said, sometimes I only need to repair and compact, but my question is based around the situation when reparing and compacting is not enough.....
When I need to decompile also, should the decompilation be done before or after the repair and compact?
"It's not happening regularly. Only once a month on average"
I think you woud find that most people here would regard that as totally unacceptable.
Corrruption should occur once in blue moon, not once in a new moon (that's quite clever, ho ho ho, very sorry !!!).
Can I ask what you mean specifically by your use of the term 'publish'. Is this more than distributing a new copy or placing the latest copy in a known location?
Are you the only user? I can't work that out from your post. If there are other users do they get the same problem?
Is the problem occurring while you developing, or while you are using the application or both?
I think you woud find that most people here would regard that as totally unacceptable.
Corrruption should occur once in blue moon, not once in a new moon (that's quite clever, ho ho ho, very sorry !!!).
Can I ask what you mean specifically by your use of the term 'publish'. Is this more than distributing a new copy or placing the latest copy in a known location?
Are you the only user? I can't work that out from your post. If there are other users do they get the same problem?
Is the problem occurring while you developing, or while you are using the application or both?
<<
Are you the only user? I can't work that out from your post. If there are other users do they get the same problem?
>>
Along the same lines as Pete's question... IF you have multiple users, do they all have their own local copy of the Access front end?
(The correct answer as a means of avoiding corruption is "Yes!" :-) )
--->>> once in blue moon, not once in a new moon
I love it!
Are you the only user? I can't work that out from your post. If there are other users do they get the same problem?
>>
Along the same lines as Pete's question... IF you have multiple users, do they all have their own local copy of the Access front end?
(The correct answer as a means of avoiding corruption is "Yes!" :-) )
--->>> once in blue moon, not once in a new moon
I love it!
ASKER
When i say publish, I mean create a .ACCDE and distribute it. After the crash, usually my .ACCDB seems perfectly ok, but when I distribute a fresh .ACCDE the users cannot open it. So I have got into a routine whereby I always test the .ACCDE after creating it. Just to make sure it is usable.
I am the only developer. There are about 15 users who have runtime access installed to open the .ACCDE
The crash seems to happen exclusively while I am saving the current object. So it's not happening as a result of normal usage of the application.
I am the only developer. There are about 15 users who have runtime access installed to open the .ACCDE
The crash seems to happen exclusively while I am saving the current object. So it's not happening as a result of normal usage of the application.
ASKER
And just to be clear, each user has their own local copy of the .ACCDE
That post casts a completely different light on things. So you are talking about 'corruption' arising during development, not in a production system. Is that correct?
As Pete said, once a month seems way too frequent.
Make sure that everyone has the latest service packs/all hotfixes installed. This seems similar to the issue you're seeing under Access 2007:
http://www.fmsinc.com/MicrosoftAccess/Access2007_SP1/
Regarding correct order of compact/repair and decompile, I would start with compact/repair (as a given first step). Then I'd follow these instructions which are consistently recommended by the Access Experts here - and which include compacting a couple of times...
http://www.granite.ab.ca/access/decompile.htm
Make sure that everyone has the latest service packs/all hotfixes installed. This seems similar to the issue you're seeing under Access 2007:
http://www.fmsinc.com/MicrosoftAccess/Access2007_SP1/
Regarding correct order of compact/repair and decompile, I would start with compact/repair (as a given first step). Then I'd follow these instructions which are consistently recommended by the Access Experts here - and which include compacting a couple of times...
http://www.granite.ab.ca/access/decompile.htm
ASKER
Yes. As I develop the application, the crash can happen as I am saving whichever object is currently being worked on. My development .ACCDB is always usable afterwards. But sometimes any subsequent .ACCDE created from it is corrupt. If the users have got a .ACCDE which they can open, they never experience a crash.
It's only happening once a month. I'm not too worried about it. I just want to know the correct sequence of remedial action. Should I repair/Compact before I decompile? Or vice versa?
It's only happening once a month. I'm not too worried about it. I just want to know the correct sequence of remedial action. Should I repair/Compact before I decompile? Or vice versa?
Plenty of good advice above, but one thing I have found which is often missed, is Name AutoCorrect. In 2007 / 2010 it is found in - Options - Current Database - Name AutoCorrect Options, 2003 in Tools - Options - General. Untick it after moving to a new empty db and see how it goes then.
One of many quotes from the net - 2.Turn off Name AutoCorrect before creating any objects in your database. This thing is buggy, incomplete, performance degrading, and contributes to corruption.
I have found this to be good advice.
Chris B
One of many quotes from the net - 2.Turn off Name AutoCorrect before creating any objects in your database. This thing is buggy, incomplete, performance degrading, and contributes to corruption.
I have found this to be good advice.
Chris B
ASKER
@burrcm: Actually I have that always turned off. Autocorrection is dangerous for databases. It can turn good data into bad data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@mbizup: Yes you posted within 1 second of my post. So I didn't see your post at the time. Thanks for pointing it out again.
I looked at the articles you linked. I am on sp3 of Access 2007 so I think that is the latest version. The users are all on sp3 of the runtime also. Their problem with the .ACCDE only occurs when opening a file freshly created from my .ACCDB following a crash on my pc. So I am confident the program versions are not the problem.
The article about decompiling is interesting. But this article mainly refers to Access 2000. So I wonder how much of it is still relevent.
But the bottom line seems to be that it is better to compact/repair first, then decompile, and then repair/compact again afterwards.
I looked at the articles you linked. I am on sp3 of Access 2007 so I think that is the latest version. The users are all on sp3 of the runtime also. Their problem with the .ACCDE only occurs when opening a file freshly created from my .ACCDB following a crash on my pc. So I am confident the program versions are not the problem.
The article about decompiling is interesting. But this article mainly refers to Access 2000. So I wonder how much of it is still relevent.
But the bottom line seems to be that it is better to compact/repair first, then decompile, and then repair/compact again afterwards.
ASKER
Thanks everyone for your input
Corrruption should occur once in blue moon, not once in a new moonThere should be a page for posts like these for everyone to enjoy.
Ron
Also, If this is happening regularly, you might consider importing all of your objects into a new database, or if a specific form or report seems to be associated with this problem try recreating that specific object.
Regarding /decompile and why I would try other things first, take a look at Jim Dettman's article, and the warning note towards the end of it:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2043-Decompile-What-it-is-what-it-does-and-how-to-use-it.html
(You should always start by making a backup of your database)