Access Database Efficiency

Microsoft Access is an efficient IT solution for creating simple databases to address some of your company's needs, especially if you have a limited budget. The tips I offer in this article will help you create more efficient databases and maximize processing time.

TIP 1: Always close Access when it's not in use.

Keeping your Access database open and minimized when you're not using it is convenient, but doing so can lead to database corruption, particularly if you have a shared database on your network. Closing database files when you aren't using them and exiting Access not only frees up resources and connections to the database, it also avoids database damage that can occur if your computer is unexpectedly shut down.

TIP 2: Determine who is using a database.

If you need to back up a database, or if you need exclusive access to it, you can determine whether any users are using the database files. Access tracks user-connection information for the database in an .ldb file, which resides in the same folder as the Access application. The .ldb file lists the logon and workstation names of all the users who are currently connected to an Access database file. If the .ldb file doesn't exist, no one is connected to the database and you should be able to access the database exclusively or perform a backup. If an .ldb file exists, you can open it by using Microsoft Notepad or WordPad. If you can't open the file with these accessories, copy and save the file with a new filename, then try to open it.

TIP 3: Routinely back up your databases.

If you're working in a network environment, make sure your database is backed up properly during scheduled network backups. Before you run a backup, verify that all database files are closed; otherwise, the backup job might skip the open database file. Before you begin to modify a database file, consider copying the file to a backup folder. You can also do a quick backup by selecting Tools from the Access toolbar, then choosing Database Utilities and Back Up Database.

TIP 4: Compact your databases to prevent database corruption.

Compacting your database is one of the best ways to prevent database corruption and improve runtime performance. Similar to disk defragmentating, database compacting rearranges and optimizes used and unused disk space in a database by removing your old Access files and replacing them with compacted versions. If your Access database is on NTFS, it's best to set up folder permissions and use Access database user-level security because the compacting process will reset any file-level permissions to their default settings. To compact your database, start by backing up the database. Select Tools from the Access toolbar, then Database Utilities, and choose Compact and Repair Database. If you're using a Visual Basic for Applications (VBA) script, use the following command to compact a database:

Application.CompactRepair(SourceFile,  DestinationFile, LogFile) 

TIP 5: Split databases into a front end and a back end.

When designing a multiuser Access database, consider splitting information into two pieces—a front end and a back end—to maximize the 2GB database size limit and achieve better performance. For example, your front-end database might contain your forms, reports, queries, and modules, and your back-end database might contain the data tables. Splitting the database also makes entering new data or modifying existing data easier. For example, after the split, if you want to enter changes on forms and reports, you need to update only the front-end database, leaving the back-end database untouched. To split a database, select Tools from the Access toolbar, then Database Utilities, and choose Database Splitter.

If you're working on a WAN, it's more efficient to load a front-end database rather than to load a database that contains everything (i.e., forms, records, and tables). To prevent database corruption in a multi-user environment, I recommend compiling your front-end database as an MDE database (i.e., an Access database) by selecting Tools from the Access toolbar, then Database Utilities, and choosing Make MDE File. Then, distribute the MDE file onto users' local machines.

TIP 6: Create a primary key to prevent duplicate records.

One fundamental database design rule is to keep your database free of duplicate records. An easy way to avoid duplicate records is to define a primary key or set of primary keys that uniquely identifies each record in a table. The primary key also functions as a unique index or ID. Some examples of primary keys are item number (or AutoNumber), Social Security Number, and location. Access offers three types of primary key data types: AutoNumber, single-field, and multiplefield. The most common primary keys are Auto-Number and single-field.

TIP 7: Use index entries for faster processing rates.

Using index entries lets the database more quickly sort and retrieve records in a table. The field used as an index is often determined by which field (or fields) the WHERE clause uses most often in queries. You can indicate whether the index field should be unique or allow duplicates by selecting Yes (unique) or No (allow duplicates) in the index's Unique property field. If a database table's primary key is set up as an AutoNumber data type, you should set the Unique index property to Yes to prevent a user from entering the same record twice. You can also set an index entry as single-field or multiple-field. Remember that an index creates overhead in both disk space and processing time, so avoid creating unnecessary index entries.

TIP 8: Reset the value of the AutoNumber field to 1.

The AutoNumber field is one of the primary key fields in Access. Access automatically assigns the next number for each new record according to the value (i.e., Increment or Random) selected for the New Value property field. (Note that null is not a valid entry for the AutoNumber field.) You can't manually enter or edit the value in an AutoNumber field, but you can reset this field to make it start renumbering entries beginning at 1, even if the table contains existing records. To transfer records out of a table, create a backup copy of the table by using the Copy or Save As function and delete all the existing records in the table. Select Tools from the Access toolbar, then Database Utilities, and select Compact and Repair Database while in the empty table. After you append the old records from the backup table, you should see the AutoNumber field restarted at 1.

TIP 9: Use the CanGrow and CanShrink properties for large text fields.

You'll find CanGrow and CanShrink properties on controls such as subreports, subforms, and textboxes, as well as on report sections. By default, the CanGrow property is set to No. To increase the vertical field length to accommodate the size of the data, set the CanGrow property to Yes. Report and form designers can't always predict the actual size of text in a record, so this setting is useful when you need to display large text fields, such as a memorandum. Verify that you have enough space around the field when positioning the control on the design view; otherwise, the CanShrink property won't function properly. For more information about custom functions you can use to eliminate unwanted spaces, see the Microsoft article "Eliminating Unwanted Blank Lines in Labels and Reports" at http://office.microsoft.com/en-us/assistance/ HA011494721033.aspx.

TIP 10: Avoid updating the wrong reference library.

Are you supporting multiple Access databases or projects that users are developing through different versions of Access (e.g., Access 2003, Access XP)? Access 2003 lets you open Access databases in different versions. However, you need to ensure that you store separate copies of the reference libraries that the database file on your workstation uses. Otherwise, if you were to use Access 2003 to open and edit your Access XP database file, the Access XP library could be updated automatically to Access 2003 reference libraries, causing some of your VBA code to function improperly. Using the VBA editor, you can access your reference library by selecting Tools, then References. To be on the safe side, it's best to develop your Access database by using the same Access program version and patches that your users use.