MS-Access Tips for Serious Users

Provided by Allen Browne, allen@allenbrowne.com

A more recent version of this page is also available.

Database Corruption

Since the Access database is a single mdb file, you can lose everyting 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 uses swapfile space dynamically, and can fail without warning if space is inadequate.
• The user who reboots the computer because "Access is not responding", when code has turned Echo Off. (Hint: During development, create an AutoKeys macro that turns Echo On again.)

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/ME 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

All Versions

• Access's syntax checking often prevents crashes. Be especially careful with anything that bypasses syntax checking, such as strings passed to Eval(), and calls to the Windows API.
• After upgrading from a previous version of Access that contains any code, open a module and choose "Compile All Modules" from the "Run" menu.
• If your application frequently performs Make Table queries, consider writing this temporary data to a separate mdb. This will reduce the number of writes to your main mdb data file.

Access 2 (Windows 3.xx)

• Make sure you have the installed the free Jet 2.5 Service Pack.
• 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, or foreign entries in the "[386Enh]" section of System.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.

Access 95

• Early releases of Access 95 had an unworkable copy of vba232.dll. Download the free upgrade from Microsoft, named vba232a.exe.
• In certain circumstances, the Relationships window can fail to display some relationships, even though the relationships exist (as you discover if you try to delete the related tables). The only solution appears to be to delete all relationships in code. Anecdotally, I suspect this problem is more likely with self-joins. Here is the code to delete all relationships.

Access 97, 2000, 2002

• In VBA code, make sure you close everything you open, and set objects to Nothing before exiting. Although Access should handle these things when objects go out of scope, failing to explicitly do so can render Access unclosable (short of Ctrl+Alt+Del).
• The modules behind forms seem especially susceptible to corruption in Access 97. The form becomes unopenable due to compilation errors, try starting Access with the "/decompile" command line switch. If that still fails, delete the form and compact the database. This seems to occur only while the form and its module are being changed regularly (i.e. during development, not end use), particularly if the form's module is edited while the form is open (not in Design view).

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.