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:

• Split your database. Rather than sticking every object your database uses in one massive MDB file and sharing the whole thing over the network, split the database into two pieces: the front-end forms, reports, queries, and data access pages, and the back-end tables containing your data. Place a copy of your front-end database on the local hard disk of each machine and link back to the tables that are sitting on a fileserver. This means that far less data has to be dragged around the network and exposed to corruption. Your database will also run more quickly and will allow you to make design changes to the front end while still using live data.
• Use decent network cards. The wafer of plastic and silicon that is imbedded in the motherboard of the unbranded, bargain basement PC in the corner of the room is simply not up to the job of moving large chunks of data around. Stick to well-known brands of network cards. As with all network equipment, it’s best to match the manufacturers of all your NICs and, if possible, use the same brand as the hubs or switches.
• Keep your device drivers updated. Device drivers win the prize for worst-written software ever, so keep them as up to date as possible. PC manufacturers pay little attention to device drivers. In fact, 80 percent of crashes in Windows NT4 are, according to Microsoft, caused by badly written device drivers. Because so many people report bugs and problems with drivers, they’re updated very quickly, so it’s worth doing a periodic check to see if updates have been released.
• Replace any suspect network equipment. Patch cables are not expensive. Use the “snagless” types, which are less likely to be bent and disconnected if someone pulls the cable too tight. If you’re using a hub to connect your network, then consider replacing them with switches. Although this approach is drastic, it does bring other advantages to your network, the most noticeable being speed. Switches break the LAN into separate “collision domains,” meaning that fewer collisions occur and so fewer packets have to be present across the wire. (A “collision” is when two machines on the wire talk at the same time, and their data packets collide, having to be re-sent.)
• Take a close look at your virus-checking software. Any kind of TSR style “real time” virus checking can cause corruption in all kinds of files, Access more than any other. If you have a real-time monitor on your network, disable scanning of MDB files. Do remember though, MDB files are a good spot for virus writers looking to hide malicious code, so scan anything that comes into your network before letting it loose on your users.
• Use the Compact and Repair utility regularly. Access files never get smaller, even if you delete data, until you compact them, and a larger file is a bigger target for packet loss and other causes of corruption over a network. If you have any data in your user’s databases, set it to “Compact On Close” in the Options menu. If you’ve split your data onto a separate back end, then compact it regularly. It’s even possible to do this with VBA code, so you could set it to happen at a certain time, say midnight, if at all possible.
• Don’t leave the database open. Most backup software will back up Access databases even if they’re open, so it’s tempting to leave them that way. This only increases the possibilities of corruption, so get your users to close their applications, even over lunch hours.
• Defragment your hard disk. MDB files often get quite big, and because they change in size so often, it’s not unusual for them to be split into several hundred fragments. (I think 1,200 was my record, on a 60-MB MDB file.) This goes hand in hand with having a big-enough hard disk. Disks with little or no free space will become fragmented more quickly, and having less than 25 percent free space available on the drive will cause any defragmenter to work less efficiently.
• Upgrade to SR-1 of Access 2000. While Microsoft is very quiet on the subject of database corruption, it’s just possible that it may happen less in SR-1. I don’t have any proof, but it doesn’t hurt and it may stop Access from crashing so often, which is a common cause of corruption. It’s also worth downloading updates to the JET database engine. This is part of the Microsoft Data Access Components (MDAC) package, which is updated regularly and can be downloaded from Microsoft’s Web site.
• Remember to close the connection. This may sound obvious (and I’m sure you’re all doing it anyway), but this is important if you use ADO or DAO to connect to an Access database. One little rs.Close statement is all it takes, but without it, you’ll get a database which is corrupted twice, three times, or even more each day!

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: