10+ ways to keep your Access database in tiptop shape

A sluggish database can cost your money and waste users' time. Here are some maintenance tasks and general best practices to ensure your database is healthy and operating at peak efficiency.

A sluggish database can cost your money and waste users' time. Here are some maintenance tasks and general best practices to ensure your database is healthy and operating at peak efficiency.

A database that's offline or responding slowly can mean many things -- not the least of which is a loss of revenue. Certainly, slow performance is a nuisance to users who depend on the information. But with just a little work, you can keep your Access database humming along. The key to maintaining a stable Access database is to stay on top of the regular maintenance and adopt some best practices in database design and management.

Note: This article is also available as a PDF download.

1: Compact

Access is the most popular desktop database, despite the fact that it's vulnerable to corruption, which often results in lost data. The easiest way to avoid corruption is to compact the database regularly. The compact process makes a copy of the file, overhauls its objects, deletes temporary data, and rearranges the fragmented pieces.

You can manually compact by choosing Database Utilities from the Tools menu and then selecting Compact And Repair Database. In Access 2007, click the Office Button, select Manage, and then choose Compact And Repair Database from the Manage This Database list. You can train users to do this, but depending on them to remember might be problematic. In addition, it's a good idea to create a backup before you compact and repair.

To automate the process, you can force Access to compact every time the database is closed. Unfortunately, this process sometimes leaves temporary files (named db1.mdb, db2.mdb, and so on). These files are copies of the database that Access creates during the compact process. At the end of the process, Access deletes the original and renames the temporary file (the compacted version). If Access can't delete the original, it doesn't delete the temporary file. Assume that the compact failed if Access doesn't delete the temporary file. When security is an issue, you should know that anyone with access to the folder could access the copy left behind by the compact process. Remove these copies as they occur, or avoid the compact on close method.

To enable the compact-on-close feature, choose Options from the Tools menu and click the General tab. Check the Compact On Close option and click OK to return to the database. In Access 2007, click the Office button and select Access Options. Click Current Database in the left pane and then check the Compact On Close option in the Application Options section.

Keep in mind that you can't compact a shared database while someone else has it open. That complicates things, especially if you try to automate the process using a VBA function because the function must detect and close any open instances of the database before compacting it. For that reason, most developers must customize their compact code solutions. The VBA Help files offer two generic code solutions. In the Visual Basic Editor (VBE), launch Help and search on compact database method. Help will return a DAO and a RJO example.

2: Use a simple and consistent design

When designing a new database, keep simplicity and consistency in the forefront. A simple and consistent design is easier to maintain and modify. In addition, users will learn how to use consistent features quicker. In this case, a little preparation and thought will produce a database that's easy to maintain and modify.

3: Stick with standards and conventions

Consistent standards and conventions allow developers to work in tandem. Many development teams have in-house conventions and standards. For instance, most Access teams use the Leszynski/Reddick convention to name native objects and variables. Using predefined prefixes helps developers who come after you discern an object's type and general purpose. Similar to a consistent design, standards and conventions produce a database that's easy to maintain.

4: Document

Documenting a database shares your methodology with other developers and can help users as follows:

  • Table and field descriptions define the entity and describe the data's purpose. Access displays a field description in the Status bar. That means the user can quickly discern the field's purpose by glancing at the status bar.
  • Code comments explain your decisions when more than a glance is required.
  • Help files guide users while using forms and reports.

The best documentation is succinct and uses proper grammar. Providing the appropriate documentation will help other developers modify your database and guide users.

5: Schedule backups

With a large relational database system, like SQL Server or Oracle, the administrator schedules and maintains a backup routine. Access doesn't require an administrator, so your first decision might be who should be in charge of backups. Then, you have to decide where you're going to store the backup file. Of course, you shouldn't store it on the same hard drive with the production database. If something goes wrong with that drive, you'll lose both.

It isn't enough to back up your database occasionally (whenever you can find the time). Schedule backups regularly and adhere to that schedule. Use the operating system or a third-party product to avoid the hassle of backing up your database manually. If data is critical, every few minutes isn't too often. It seems like overkill only until you need a comprehensive backup and there isn't one.

To create a backup in Access 2003, click Back Up Database on the File menu. Or choose Database Utilities from the Tools menu and then select Back Up Database. In Access 2007, click the Office button, select Manage, and then select Back Up Database from the Manage This Database list. In both versions, Access will display a default name -- a combination of the database name and the date -- in the Save As dialog box. Most of the time, the default name is adequate, but you can change it. Just click Save to make a copy of the current database. (Note: You can also use Internet Explorer to create a copy of the database.) If you implement user-level security with Access 2003, remember to back up the workgroup information file. User-level security isn't available in Access 2007.

6: Defragment your hard disk

A severely fragmented file (or drive) is slow. A busy database compounds the problem because all those transactions create even more fragmented pieces. For that reason, you should defragment your hard drive on a regular basis. In Windows XP, do the following to defragment a system's hard drive:

  1. Choose All Programs from the Start menu.
  2. Select Accessories.
  3. Select System Tools.
  4. Select Disk Defragmenter.
  5. Select the drive and click Defragment.

This process can take a while, so schedule it for a time when the system won't be in use. Every program and file you have will benefit -- not just an Access database. However, due to its size, an Access database is more vulnerable to fragmentation slowdown.

7: Split the database

Access is a desktop database, so its file structure is different from the larger relational database system files. Specifically, you can store data and interface objects in the same file. However, just because you can doesn't mean you should. In fact, it's best to split your database into two separate files: a database that contains the data (backend) and a database that contains the interface objects (front end).

Most users working in the front end are oblivious to the backend. This setup protects your schema (table design) and it's easier to maintain. When it comes time to upgrade the database, you simply replace the old front-end file with the enhanced version. If you store data and interface objects in the same mdb file, you must import data from the old file into the new one, which is inefficient, especially if you have to distribute an upgrade to numerous users.

To split a database, choose Database Utilities from the Tools menu. Then, select Database Splitter. The wizard will walk you through the process. In Access 2007, click Access Database in the Move Data group on the Database Tools tab.

8: Maintain a secure list of passwords

If you password-protect your database files, keep a list of all passwords and store that list in a secure place. Share the list and its location with someone you trust. You might not need the list, but in your absence, such a list could be invaluable. Keep in mind that "in your absence" doesn't have to mean you no longer have a job with that company so you don't care if users can access the database. You could fall ill, have an accident, or just be on vacation when a user forgets a password or a new hire needs training.

9: Audit users

Periodically, update authorized users. You might discover that some employees who are no longer with your company still have access. Other users may have moved to different departments and might have retained permission to access a database they no longer use. You should update permissions when changes take place, but an occasional audit helps you find changes you forgot or missed.

10: Check connectivity

If users share a networked database, check the connection at the start of each workday. Troubleshooting a connectivity problem before users start trying to log in will be much easier. In addition, you'll avoid frustrated users.

11: Avoid undocumented functionality

Try not to use undocumented features, because Microsoft doesn't support them -- you're on your own. In addition, there's no way to guarantee that the feature will exist or behave the same way in the next release or service pack. When undocumented features disappear, it's difficult to troubleshoot because there's no warning or explanation. If you do decide to use an undocumented feature, be sure to document it thoroughly yourself.

12: Avoid using system tables

System tables contain a lot of information about your database schema, and sometimes accessing that information is simpler than a more traditional alternative route. Unfortunately, new releases often bring changes to these tables, which break your application. Avoid using system tables unless you have good reason to do so and then, carefully document your choice.

Finally: 10 Things... the newsletter!

Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know lists featured in TechRepublic's 10 Things newsletter, delivered every Friday. Automatically sign up today.