Apr 2, 2002 | Luke Mason | E-Mail

An ounce of prevention really is better than a pound of cure, particularly for an IT pro. In the case of Access databases, there are steps you can take that will prevent or limit database corruption problems. Although these prevention measures take time, following this strategy will help limit database corruption problems.

The following strategies, presented roughly in order of importance, should be used to prevent corruption of Access databases:

Catch up with part one of the Access database series
“Access corruption: Searching for a cause”

Last-ditch efforts
Apparently, Windows 2000 corrupts Access databases more often than other operating systems. Surprisingly, Windows 98 is corrupted less often than NT. If you think it’ll work for you, then it might solve some problems. I can’t say categorically whether Win2K does pose more of a threat than NT 4, partly because Microsoft is completely silent on the possibility. From experiences of my friends and colleagues, and from my own when developing with Access 2000, I’d say that there is enough of a chance to make it worth sticking to NT4—assuming that you have the option. It could be that this difference between the two operating systems is due to device drivers being more established on NT than 2000. With the imminent release of Windows XP, it could be that updates to device drivers will stop being written for NT4, and Windows 2000 will have better support.

Finally, take a good look at your fileserver. Having spent weeks trying to track down the cause of corruption in two Access databases, I happened by chance to move the back-end tables to a different server. The original fileserver is an old PII 400 workhorse, poorly specified from the start and sporting a motherboard that is only capable of taking 128 MB. It seemed to work fine as an e-mail server and fileserver to keep hold of our production databases, as it was backed up every night and had a fast SCSI hard disk in it. The CPU load never averaged more than 15 percent, and although the memory was pushed, it still had enough to function without hitting the swap file very often.

When the databases were moved to a more solidly built Compaq Prosignia server, the corruption problems vanished. I don’t know for sure what caused the corruption on the old fileserver. I know I can count out device drivers and the network card, and the LAN itself is rock-solid. My best guess is that in one direction the processor wasn’t quick enough to keep up with the flow of data between the network card and the SCSI controller. If anyone has any interesting ideas why this should have happened, then send me an e-mail. You’ll be glad to know that since the databases have found a new home on a dual PIII 850 server with 600 MB of memory, I haven’t heard a peep out of them.

Created Date: 04/25/2002  Last Reviewed: 04/25/2002  Rev. Date: