Microsoft Access Tips for Serious Users

Provided by Allen Browne.  Last updated: April 2010.

A superseded version of this page is available if you are using Access 97 or earlier on Windows 95, 98 or ME.

Preventing corruption

Since the Access database is a single mdb file, you can lose everything if it becomes corrupt. This is a rare occurrence if the database is running on reliable local area network (or single computer) and database objects are not being modified.

Corruption is much more likely during development when forms and reports are being created and modified, and code is being written and debugged.

For suggestions on recovering a corrupt database, see recovering from corruption.

Preventing corruption of installed databases

The easiest way to corrupt an Access database is to interrupt a write operation. The inelegant disconnection accounts for almost all corruptions.

If you experience corruption on a database that is not being modified (i.e. only data is changing, not forms, reports, ...), check these areas:

Some basic steps can lessen the chance of serious loss: adequate backup procedures, periodic compacting of the database, and simply closing the database when not in use.

Preventing corruption during development

Your database will corrupt during development when objects and code are constantly being designed and tested. These tips will reduce but not eliminate corruption:

1. Install the latest proven service packs for your version of Access and JET.
2. Turn off Name AutoCorrect before creating any objects in your database. This thing is buggy, incomplete, performance degrading, and contributes to corruption. Turn if off under File (the Office Button) | Options | Current Database (Access 2007 and later), or in older versions: Tools | Options | General.
3. In your subforms, include a text box for each field in its LinkChildFields property. This simple step avoids frequent of Access 2002 and 2003 ("... shut down by Windows..."), where the form is based on a query (not directly on a table.) The text boxes can be hidden if you do not wish to see the foreign key fields.
4. Never allow multiple developers to modify the objects in a single file at the same time. Give each one a local copy, and import their objects back to the main copy later. This tip alone will save you much grief.
5. Do not edit the text of a module while it is running (i.e. in break mode). If a project is compiled, and you edit the text while it is running, the Compile item on the Debug menu may remain greyed out. This may indicate that Access has become confused between the different copies it keeps of the code: the compiled code, the text-version of the code, the text-version of the temporary copy that it creates when you edit the form's module, and the version that is actually running. Refusing to edit code in break mode significantly reduces corruption in my experience.
6. Watch out for the first hint of corruption, and take action. A project is beginning to corrupt if the Compile doesn't hold. If a compiled project is uncompiled after a restart, it is time to backup, compact, decompile, compact again, compile, and make another backup copy.
7. If Access crashes, do not allow it to compact and reopen. Delete the ldb file. Create a copy of the crashed database. Then reopen, and compact/repair. If it did corrupt, it may be possible to rescue some objects from the corrupted database provided it has not been compacted. The undocumented commands SaveAsText and LoadFromText are useful in this context.
8. Create a backup copy and compact regularly during development. Our pattern is twice a day, so we never lose more than half a days work.
9. In your code, close everything you open and dereference your objects. If you do not open something (such as the default Workspace, or the RecordsetClone of a form), do not close it, as that can cause problems. If you do open something (such as OpenRecordset), close it. Then set all object variables (Form, Recordset, TextBox, ...) to Nothing.
10. Be especially careful with any code that does not have the normal safety net VBA provides, e.g. Eval(), API calls, late binding.
11. Use DoCmd.Echo True in the error recovery section of any procedure that turns Echo off. This will prevent you (and the end user) from crashing out of Access because it is visually unresponsive.
12. Split the database, and give users an executable version that cannot decompile:
    • In Access 2010:  File | Save & Publish | Make ACCDE.
    • In Access 2007:  Make ACCDE on the Database Tools ribbon.
    • Earlier versions:  Tools | Database Utilities | Make MDE File
13. Unless you really need it, turn off Record-Level Locking (as opposed to Page-Level Locking.) Page-Level locking is more efficient to execute, and works more reliably in some circumstances. In Access 2000 - 2003, uncheck the box under Tools | Options | Advanced | Open Databases Using Record-Level Locking. In Access 2007 and later, go to File (Office Button) | Options | Advanced.

Rebuilding your file by importing everything into a new database is quite quick. The most time consuming part is laying out your relationship diagram again after every import. To avoid that problem, Stephen Lebans has a free utility to save and restore relationship views.