2003.10.08 10:46 PM

Access Corruption Mystery

[Update: Mystery solved.]

Investigating and fixing Access database corruptions for two days makes me grumpy, especially when I can't figure out why the corruptions are happening. In this case, the corrupted databases belong to a couple of Access apps I built for a client a long time ago, and which I recently converted to Access 2002. At the client's request, we also recently moved the apps to a Citrix Metaframe server farm so users can easily access them via their browsers from any office. Aside from the usual Access ass-kickings, this has all gone pretty well, and the apps have been running (more or less) in this configuration for the last two months, without a corruption.

Until yesterday. I got a call from one of the app's admins saying users couldn't get in. Instead, they were getting a message saying the database was corrupted and needed a repair. The admin was also quick to remind me that this was the worst possible time for a problem, because the apps are critical during the first two weeks of the month. Of course.

A quick check found the corruption in the app's code database, not its data database. (Each app has two MDBs - one for code, queries, forms, etc., and another for data. If you don't separate your Access apps this way, you should.) A corruption in the code database surprised me, as the code database is static - nothing is ever explicitly written to it. Access does occassionally write things to it (e.g., query compilations, etc.), so I suppose a corruption is not impossible (duh, I'm looking at one), but in the six or seven years these apps have been running, I've never experienced a code database corruption. In fact, I've only ever experienced a couple of corruptions in the data databases.

Not having seen this type of corruption before, and not having reason to expect another, and being under extreme pressure to get users back into the app, I fixed it. Actually, I replaced it, with a copy of the last delivered version. Being a static code-only database, there was no point in repairing it. (By the way, that's reason enough to keep your Access code and data in separate databases - code database corruptions can be resolved without touching the data; another good reason is that it makes update deliveries a snap.)

So, everyone was fat and happy again. At least until it happened again three hours later. And then again a couple of hours after that. And then again, but this time in the other app. And then all day today in both apps.

And now I'm writing about it. Stay tuned.


Comments

Note to self, write a post about designing Access apps to reduce the potential for corruption.

ewbi.develops | 2003.10.09 10:46 AM

I have the same issue with a company out of Raleigh. I am being locked out of the database, with an error that tells the database is being updated by another user making the same changes. This is an insurance company so it is very time sensitive. I have another company in Fayetteville that has been using my software for years on a network, and has never had this problem. I can't get into the database to repair it, so the data is lost. I can't find the issue either. Unable to troubleshoot; because I can't open the database even on a stand alone machine. Did you find anything further?

Bill Harrison | 2003.10.30 12:42 PM

Hi Bill - Not much new to report (and haven't had time to blog). We trimmed the number of Metaframe servers on which these apps are published down to just two (from six) in an effort to better isolate the problem. Since then, the corruptions have stopped, but that likely has more to do with the systems getting very little use after the 15th of each month. We'll see what happens next week.

My first thought was that we had a badly configured server in the farm, or perhaps one with a bad network adapter (hence the desire to limit the servers involved). However, I've since heard of others experiencing this, so I'm more apt to think it is OS/config related (maybe opportunistic locking?). Here's a fellow reporting a similar situation via the Citrix Knowledge Center:

http://support.citrix.com/forums/thread.jspa?forumID=35&threadID=25891&messageID=55464#55464

He uses an MDE-formatted code database while we use MDB-based ones, but the fact that his corruptions occur in that (the code database) is similar to our problem. The writer does not indicate whether this just began to happen or has always happened, and there is no resolution noted. Interestingly, he has allowed access to the database via Citrix and over the network and only experiences a problem over Citrix.

Please let me know if you discover anything else, and thanks for taking the time to write.

ewbi.develops | 2003.10.30 01:33 PM

I have been researching a little more, but the Raleigh company is a bank, and therefore is very cryptic about security. The Fayetteville based company has 25 users, and runs like a top, but with less security. I am wondering if there are issues related to whatever firewall protection, and mapping. I am currently researching utilities to repair. 8 hours of work lost can mean millions. Do you know of any utilities?

Bill Harrison | 2003.10.30 03:08 PM

I didn't until your message prompted me to look. Seems MS has spawned an entire mini-industry around repairing corrupted Access databases. Below are some products and services I found. Note that I have *not* used any of them and have *no* opinion about them one way or the other. However, if you wind up using one or more of them, please let me know how it goes.

Products:
http://officerecovery.com/access/
http://www.ontrack.com/easyrecoveryfilerepair/index.asp

Services:
http://www.accessdatabaserepair.com/
http://www.datarevive.com/
http://www.database-repair.com/html/englisch.html

In my experience, I've never had a corruption so bad that Access wasn't able to recover it to the point of making it accessible again (knocking on wood). However, I have experienced some (minor) lost data following an otherwise successful Access repair.

By the way, you didn't mention whether your app was divided into separate code and data databases.

ewbi.develops | 2003.10.30 08:22 PM

We have been expereincing the sam problems for months and are still under invetigation. I have trying moving the *.MDE front-ends and *.MDB back-ends to different servers. Installed Hotfixes and Service packs till I turn blue. The only improvemnet we have found is training the
users. After months of invetigation most of corruptions are due to users abnormal terminating the database. A monitor was put on the datbase to record abnormal exits.
It appears that nearly every user was exiting the database abruptly. Our fix was to write an exit button on to the main menu of the database, establish a warining banner, and disable the application toolbar through CITRIX. Since all these changes were made we have been fortunate to have approximately 1-2 corruptions a day in lieu of 6 or more. I would like to try the OPLOCK idea, but I still think the problem is the user just closing his web brower or *important* being timed out of CITRIX, which is the same as a non-graceful shutdown of the application.

Ronnie Riddett | 2004.02.17 07:19 AM

Ronnie - Thanks for sharing your experience. I'm finding more and more folks having these problems. If you haven't already, check out what we did to solve our problem (still no corruption since December):

http://ewbi.blogs.com/develops/2004/01/access_corrupti.html

Good luck.

ewbi.develops | 2004.02.17 10:48 AM

On top of the solutions listed there is an additional software option called AccessFIX. Being the most widely used I thought it should be listed with the other available commercial solutions. You can download the demo version of the product at http://www.cimaware.com . Of the the software solutions listed only AccessFIX provides free technical support and a money back guarantee. That and its effectiveness should help people make up their minds.

Cimaware Software | 2004.11.10 05:09 AM

Thanks - hadn't heard of Cimaware, will have to check it out. Just to be clear, your AccessFIX software is not a solution to the Metaframe-related corruptions, it's an alternative approach to repairing already corrupted databases, right?

ewbi.develops | 2004.11.10 05:14 AM

Generally te software is much more expensive than a recovery service. My company offers full recovery of tables, queries, forms, reports, modules etc. from as low as £29.99 (US $55) with a free no obligation recovery report.

Check out our website that also has links to tutorials on free alternatives to try before sending your corrupt file for repair;

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

Wayne Phillips | 2005.03.15 12:55 PM


TrackBack

TrackBack URL:  https://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83456beda69e2

Listed below are links to weblogs that reference Access Corruption Mystery: