Access Database gets corrupted on a regular(ish) basis

[PeteB2002]

We have Access2000 database with around 18,000 records, shared from a server to around 7 people.
Access is set to lock the file for editing to prevent more than one person editing the same record.
Every now and then, we get the database reportingit is 'in a state' whichinevitably means it is orrupted and we have to reload the previous day's backup tape - quite often losing 4-6 hours worth of data.

Any ideas on why it's doing it and how to get around it?

 

[thornmastr]

You might want to check out the following article in the MSKB: Q209137

If you feel your problems are not solvable by this rather generalized article, please post back. The more specific you can be about the nature of the symptoms snd the interractions revolving about your corruption issues, the more specific the issues we can address. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[PeteB2002]

Thanks,
On average the database goes 'wobbly' once a month. It can't be opened at all, so can't use the compact & repair. Our only way is to recover from tape backup.
This leads me to think that it is caused by an illegal shutdown of one of the users - if it was hardware or network related I would expect it to happen more often than it does.

All I have to do is find out when it happens and who was in there (more difficult than it sounds as the database is used extensively by 7 users throughout the course of a working day)

 

[thornmastr]

Let’s see if I can get you through a few of the rough spots. It’s very rare that even the three finger salute (alt,Ctl,Del) will corrupt Access so badly you can’t even open it, but it certainly can happen. And, before I forget, you may have a malicious user who has learned that attempting to open an Access database with MS Word causes corruption of such magnitude that it is almost impossible to fix; in fact, in most cases it is impossible to fix. If this is the case the only cure is to simply fire this person and make sure the remaining staff knows that this is cause for immediate termination.

Do you have a copy of the MS JETCOMP utility which will force compression of a database even if you cannot open it. Originally, it was for A97 but it works quite well with A2K as well. You can download it at MS and it is free. As you know, compression will force a very brief rendition of the repair process as well. Quite often that is all it takes.

If that does not work for you, there is the hidden and practically undocumented (MS has pulled most references to it), command line decompile switch. This literally places your entire database in an uncompiled state which quite often instantly eliminates corruption. You should always make a copy of the database before using this option because if things go totally bonkers on you, at least you will be able to get back to a more “normalized” state of corruption. If you want to try this beasty, the format is:

Access path your db path and name /decompile

When this completes; open you db, compile it and save it.

If these steps don’t get you through the corruption issue, look for a malicious user. The probability is high you may have one.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[PeteB2002]

We have some users that will quite innocently attempt to open just about anything with Word - so that may be the very answer.

Thanks for your feedback!

 

[LissaB]

Hi! I am having the same error occur on one of our critical databases. I tried using the MS Jetcomp and it each attempt returns an error.

I would like to try your second option, but I need some more specific directions on how to do that. Where would I go to attempt this?

Thank you,
Lissa

 

[thornmastr]

I gather you are talking about decompile. First, make sure you backup that database. No matter how corrupted your db, it can get much worse.

The following information should get you through the process. Remember to make sure your path names reflect your configuration.

If you are still having problems, post back And be a bit more specific about what is happening.

WARNING: Do a backup before the /decompile. Of course you're doing daily
offsite backups so this isn't a problem anyhow, right?!?!?!

Create a shortcut with the following '"C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" d:\1access\voluntr\brier\vol_app.mdb /decompile'
without the single quotes. Note that if your path to your MDB contains a
space it must be enclosed in quotes just as the MSACCESS.EXE component.

Click on the short cut and you'll get the message "... has converted the
code in ... to your current version of Visual Basic". Hold the shift key
down when you click on Okay so the MDBs autoexec routines will not execute.
Now compact the MDB. Then go into any module and click on Debug and Compile
and Save All Modules. This last step is particularly important for
performance reasons.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[LissaB]

Robert,

I did exactly as you said and this is the path I entered: ""C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "c:\documents and settings\eban\desktop\trouble.mdb"/decompile

I get the exact same error message when Access starts up. I don't get anything about
>"... has converted the
>code in ... to your current version of Visual Basic".

I am getting the same ms jet engine error.

Am I doing something wrong?

Thanks,
Lissa

 

[thornmastr]

What is the error message you get? Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[trungvu]

I have also tried using the /decompile command and I still get the following error message:

"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

Others utility I have used in my futile attempt at opening/repairing my database: Jetcom.exe for Access 2002.

Your inputs and advice are greatly appreciated. Thx.

 

[Kraeg]

I've been having corruption trouble, about once a week. It's a 97 backend with 2002/97 frontends. Once corrupt, 97 has trouble repairing, so I repair with 2002, then convert back to 97.

I wrote a small utility using VB5 that allows the user to repair it themselves.
First, it backs up the corrupt database, and stores the record-locking information file (copies it, adding an incremented number to the file name, so later I can use these to try and trace a problem PC).
Then, it attempts to repair it (using compact method of DBEngine in DAO 3.6).

I haven't been able to test it on a corrupted database, though (do you think one will corrupt when you want it to? I've even had it running queries and switched my PC off to try to corrupt it).

The database is backed up the first time it is used each day, and from then on every record created/edited is dumped to a special directory as a text file. This is so if that day's work is lost due to corruption, it's not too much effort to import the text data into that morning's backup (assuming it can't be repaired, or fields/records aren't correctly repaired).

 

[thornmastr]

A couple of comments about Access and Access corruption. When Access says a file is corrupted, you’ve got a .5 probability of it not being corrupted. Access determines corruptibility by looking at bit 0 of byte 0 in the Access header record. If that bit is on, Access says your database is corrupted. If that bit is not on, you are not corrupted even if you are. The truth of the matter is MS has never truly defined exactly what constitutes corruption. So, if you have written a VB function that deals with corruption you have a .5 probability of it being as good as anything MS has to offer, and a .5 probability of it being a tad worse than what MS has to offer.
Having said that, my attitude is to define a course of actions to try and decorrupt based on how positive the signs of true corruption really on. The real caveat of Access corruption is try everything you think or hope will work. If things are so bad the database is flat out dead forever, you should have a backup somewhere.
In short, think it through, decide a course of action, and go for it.

And remember, the first rule for the past ten years of Access development, and for the next ten, is BACKUP BACKUP BACKUP.

Good luck
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[daniel563]

May I ask what server your database lives on? I have a Novell 4.11 server that gives me that same error if I have forgotten to set the files locks to their maximum. You can find more on this in support.novell.com. Once you max the locks out, the error wonderously goes away.

 

[PeteB2002]

Thanks,
We have upgraded the server - more memory and Win2000 Server and the problem has disappeared.
It used to run on WinNT4 Server - the Database would corrupt..I would say at least twice /month !!

 

[LissaB]

Our database was VERY corrupted, but we did get it all back. I found a company online that restored it for about 400.00 and that was small change considering what a loss losing that data would have been for the company.

We have now converted the Access db into SQL and have had no problems. SQL is running on a Windows 2000 server and the old database was NT4.

Thought you all would like to know how this problem of mine turned out.

And yes- backing up is working smoothly and automatically now.

Lissa

 

[gnosis13]

OK, same subject, different track. I also had these problems and after banging my head over these solutions for months I ran into the solution by chance. It turns out that one of my users was on token ring and had a bad network patch cable.

I just happened to be standing next to the user when the database was opened for the first time after a restore from tape. The user leaned back in his chair which caused a wheel to roll over the cable on the floor....boom database corrupt. We changed the cable and have not had one corruption since.

I know this is a little off-the-path, but if you continue having trouble you might look for other solutions. A+, N+, MCP