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.
Previous
Register
