Solved

Repairing database corruption

Posted on 2013-01-23
15
374 Views
Last Modified: 2013-01-25
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
0
Comment
Question by:TownTalk
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38809035
My general approach to trouble-shooting is to start with the least extreme measure and increase from there.  In this case, I'd start with compact/repairing.  I've personally never had to decompile a database.

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:
http://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)
0
 

Author Comment

by:TownTalk
ID: 38809059
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?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38809179
"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?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38809268
<<
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!
0
 

Author Comment

by:TownTalk
ID: 38809274
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.
0
 

Author Comment

by:TownTalk
ID: 38809282
And just to be clear, each user has their own local copy of the .ACCDE
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38809299
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?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 61

Expert Comment

by:mbizup
ID: 38809314
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
0
 

Author Comment

by:TownTalk
ID: 38809316
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?
0
 
LVL 28

Expert Comment

by:burrcm
ID: 38809418
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
0
 

Author Comment

by:TownTalk
ID: 38809452
@burrcm: Actually I have that always turned off. Autocorrection is dangerous for databases. It can turn good data into bad data.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38809502
TownTalk,

<<
I just want to know the correct sequence of remedial action. Should I repair/Compact before I decompile? Or vice versa?
>>

From your comment, it's not clear whether or not you saw the article link I posted in comment http:#a38809314 , which describes sequencing C&R with decompile.
0
 

Author Comment

by:TownTalk
ID: 38809615
@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.
0
 

Author Closing Comment

by:TownTalk
ID: 38809757
Thanks everyone for your input
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38818751
Corrruption should occur once in blue moon, not once in a new moon
There should be a page for posts like these for everyone to enjoy.

Ron
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now