Using the Database Utilities

Access 2003 offers the following seven utility functions, which you access by choosing Tools, Database Utilities:

• Convert Database lets you change the version of the currently open database to the Access 97, 2000, or 2002 version by saving it as a new .mdb file. The choice corresponding to the current database version is disabled.

• Compact and Repair Database checks the database for consistency, repairs problems found, and then compacts it to save disk space. Access automatically replaces the existing database with the compacted or repaired version.

• Back Up Database is a new Access 2003 feature that opens the Save Backup As dialog and proposes to save your current database file as FileName_YYYY-MM-DD.mdb. Using the backup feature is a bit faster than making a copy with Windows Explorer.

• Linked Table Manager tests for the existence of linked .mdb or other types of data files and, if the links aren't valid, lets you change the path to the linked files. This choice is disabled if you don't have a database open.

• Database Splitter divides a single-file Access .mdb application with application and data objects into a front-end .mdb file and a back-end Jet database. This choice is disabled if you don't have a database open.

• Switchboard Manager creates a new Switchboard form if one isn't present in the current database and lets you edit the new or an existing Switchboard form. This choice also disabled is if you don't have a database open.

• Upsizing Wizard lets you move tables and queries from the current Jet database to SQL Server and, optionally, change the .mdb file containing application objects to an Access Data Project (.adp) file.

• Make MDE File creates a secure copy of the file, which prevents users from opening objects in Design view and viewing or changing VBA code. This choice is enabled only when no database is open.

If you select one of the utility operations described in the following sections when you don't already have a database open, the operation involves two dialogs. In the first dialog, you select the database in which Access is to perform the operation; in the second dialog, you type the name of the file that the operation is to create. Default file names for new files are Db#.mdb, where # is a sequential number, beginning with 1, assigned by Access.

Compacting and Repairing Databases

After you make numerous additions and changes to objects within a database file especially additions and deletions of large amounts of data in tables the database file can become disorganized. When you delete a record, you don't automatically regain the space in the file that the deleted data occupied. You must compact the database to optimize its file size and the organization of data within the tables that the file contains. When you pack an Access file, you regain space only in 32KB increments.

To compact the current database, do the following:

1. Open the database you want to compact.

2. Choose Tools, Database Utilities, Compact and Repair Database. Access immediately closes the database and begins compacting it.

When Access finishes compacting the database, it opens the database and returns you to the Database window. Your compacted database is stored with the same name it had before you compacted it.

If you want, you also can compact a database and save the compacted database in a different database file by following these steps:

1. Close the open database.

2. From the Tools menu, choose Database Utilities, Compact and Repair Database. The Database to Compact From dialog opens, as shown in Figure 3.19.

   Figure 3.19. The default location for storing Access databases is My Documents. If your source database is stored in another folder, use the Database to Compact From dialog's My Recent Documents option to select it for compacting.

   

3. Double-click the name of the database file that you want to compact or click the name and then click Compact to open the Compact Database Into dialog.

4. Navigate to the folder you want to save the compacted files in. (This compacted version is saved in the ...\Office10\Samples folder.) You can't save the compacted file to the Recent folder.

5. In the File Name text box, type the name of the new file that is to result from the compaction process (see Figure 3.20). If you choose to replace the existing file with the compacted version, you see a message box requesting that you confirm your choice. Click Save.

   Figure 3.20. If you opened the source file from the History option's list, make sure to navigate to the correct folder to hold the compacted version.

   

Access then creates a compacted and repaired version of the file. The progress of the compaction is shown in a blue bar in the status bar. If you decide to use the same filename, the new file replaces the source file after compaction.

Caution

If the compaction process fails, your database might be damaged. Databases damaged in the compaction process are unlikely to be repairable. So, you should not use the preceding process to compact the database into a new database with the same name. Do so only after backing up your database by copying it with a different name in the same folder or with its original name in a different folder.

A database can become corrupted as the result of the following problems:

• Hardware problems that occur when writing to your database file, either locally or on a network server

• Accidentally restarting the computer while Access databases are open

• A power failure that occurs after you make modifications to an Access object but before you save the object

Occasionally, a file might become corrupted without Access detecting the problem. This lack of detection occurs most frequently with corrupted indexes. If Access or your application behaves strangely when you open an existing database and display its contents, try compacting and repairing the database.

Periodically compacting and repairing production database files usually is the duty of the database administrator in a multiuser environment, typically in relation to backup operations. You should back up your existing file on disk or tape before creating a compacted version. When you're developing an Access 200x database, you should compact and repair the database frequently. Access 200x databases that are not compacted grow in size much more rapidly during modification than with Access 97 and earlier versions.

Tip

To compact the current database automatically each time you close it, choose Tools, Options, click the General tab of the Options dialog, and mark the Compact on Close check box of the General page. Access 2000 added the automatic compaction option.

Converting Databases to Access 2002 Format

To convert prior Access version .mdb database files, .MDA library files created with Access 1.x or Access 2.0, and .mda library files created with Access 95 or 97 to the new database format of Access 2002, close any open database. Choose Tools, Database Utilities, Convert Database, To Access 2002 File Format. The process of converting database files from earlier versions of Access database formats to that of Access 2000 or 2002 is almost identical to the second file-compaction process described in the preceding section. The only difference that you'll notice is that the names of the dialogs are Database to Convert From and Database to Convert Into. Chapter 32, "Upgrading Access 97 and 2000/2002 Applications to Access 2003," covers this conversion process in detail.

Note

If you encounter error messages when converting your Access 97 or earlier .mdb file to Jet 3.0 format, see the "Compile Errors in the Convert Database Process" topic of the "Troubleshooting" section near the end of the chapter.

Converting Databases to Access 97 Format

To convert Access 200x data .mdb files to Access 97 format, open the Access 2000 database that you want to convert and then choose Tools, Database Utilities, Convert Database, To Access 97 File Format. Access displays the Convert Database Into dialog. In the File Name text box, type the file name to convert into and then click the Save button.

Tip

You must have a copy of the Access 97 object libraries on the machine on which you perform the version downgrade, and the libraries must be registered. The easiest way to accomplish this is to install Access 97 on your computer.

You can convert most data-only .mdb files to Access 97 format. Conversion of application objects is likely to result in conversion errors, which can be difficult to fix.

Creating .mde Files

An .mde file is a special version of an Access .mdb file. In an .mde file, all VBA code is stored only in compiled format, and the program source code for that database is unavailable. Also, users can no longer modify forms, reports, queries, or tables stored in that database, although those objects can be exported to other databases. Typically, .mde databases are used to create libraries of add-in wizards; create custom database applications intended for commercial or in-house distribution; and provide templates for forms, reports, queries, and other objects for use in other databases.

You can convert any Access 2002 .mdb database to an .mde file by choosing Tools, Database Utilities, Make MDE File. You must covert files in Access 2000 format to 2002 format to enable the Make MDE File choice. It's uncommon to convert a single-file .mdb to .mde format; .mde files are intended to protect application objects, not data objects. If you have a database open at the time you select this command, Access assumes that you want to save the current open database as an .mde file and immediately displays a Save MDE As dialog. This dialog is essentially the same as any Save As dialog.

Tip

Access 2000 users can't open an .mde file created from an Access 2002 .mda file. If your secured application must support Access 2000 users, use Access 2000 to create the .mde file.

If you choose Tools, Database Utilities, Make MDE File when no database is open, Access first displays a Database to Save as MDE dialog. Use this dialog to select the .mdb database file that you want to convert to an .mde file.

Tip

Be sure to save an archive copy of any .mda file you convert to .mde format on a removable disk, and store the archive copy in a safe place. The copy you make in .mde format is permanently altered; you can't restore an .mdb from an .mde file.