MS-Access Tips for Serious Users

Provided by Allen Browne, abrowne@odyssey.apana.org.au

Database Corruption

Since the Access database is a single mdb file, you can lose everything if it becomes corrupt. Some basic steps can lessen the chance of serious loss: adequate backup procedures, periodic compacting of the database, defragmenting the disk, and simply closing the database when not in use.

The most common cause

The single most common cause of corrupted Access databases in my experience is inelegant shutdown. This includes:

• A user who switches their computer off without closing Access and Windows. (Often they will tell you they shut down correctly, but either do not understand how to or cannot be bothered under pressure.)
• Power failures, both supply (failures, voltage dips) and human (pulling out/tripping over the power lead).
• Windows hanging (from any cause) while your database is open.
• An intermittent network cable connector, if the data file is across a network
• Running with less than 50MB of free hard disk space. Windows 95 uses swapfile space dynamically, and can fail without warning if space is inadequate.
• The user who reboots the computer because "Access is not responding".

After every crash, perform this sequence:

1. Make sure Access and other programs are not running.
2. Delete any files in the temp directory. (Type SET at the DOS prompt if you are not sure where your temp directory is.)
3. Delete any .ldb files in the data directory. These contain invalid locking information from before the crash.
4. Run ScanDisk to check for logical disk errors. Windows 95b/98 users: The system automatically performs a ScanDisk after an inelegant shutdown. However, by cancelling that operation and performing step1 first, you are less likely to lose data if your file is cross-linked to a temp file. Windows 3.xx users: exit Windows to perform ScanDisk or (for DOS 5 and earlier) ChkDsk.
5. If an mdb is corrupted, compact the database using a new name (creates a copy), then repair, and compact again.

Tips to prevent corruption

Access 2.0

• Make sure you have the installed the free Jet 2.5 Service Pack. All Eagle Software installations will already have this service pack installed.
• Remove any background programs, such as TSRs loaded from Config.sys, Autoexec.bat, the Startup.grp, any entries on the "Load=" or "Run=" lines of Win.ini. Be especially suspicious of timed programs such as third party screen savers, and even WinPopup.
• Monitor system resources. Depleted GDI resources can cause the system to hang, e.g. when you attempt to print. Embedded MS Graph objects are especially resource hungry. Restart Windows periodically, as resources "leak".
• Search for duplicate DLLs. You may have found the problem if you have two copies with the same name anywhere on path, such as in Windows, Windows\System, the Access program directory, your working directory, or any other directory in your PATH statement.

Worst Case

If "repairing" does not fix the corruption, you may be able to create a new mdb file, and import most of the objects. This forces Access to re-create everything from scratch.

CALL EAGLE FOR HELP FIRST!!!