- Understanding Access Functions and Modes
- Opening the Northwind.accdb Sample Database
- Understanding Access's Table Windows
- Navigating the Home and Create Ribbons
- Using the Function Keys
- Setting Default Options
- Creating a Customized Template File
- Using Access Online Help
- Spelunking the Database Utilities
- Packaging, Signing, and Distributing an Access 2007 Database
- Troubleshooting
- In the Real WorldReading the Ribbon UI's Tealeaves
This chapter is from the book
This chapter is from the book
This chapter is from the book
Spelunking the Database Utilities
Access 200x offered eight utility functions that you could access by choosing Tools, Database Utilities. Following are the locations of these tools in Access 2007:
-
Convert Database becomes Office, Save As, Access 2000 Database, Access 2002 - 2003 Database, or Access 2007 Database. -
Compact and Repair Database moves to Office, Manage, Compact and Repair Database, which 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 moves to Office, Manage, Back Up Database and opens the Save Backup As dialog and proposes to save your current database file as FileName_YYYY-MM-DD.accdb. Using the backup feature is a bit faster than making a copy with Windows Explorer. -
Linked Table Manager moves to the Linked Table Manager command in the Database Tools ribbon's Database Tools group. It tests for the existence of linked .accdb 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 with linked tables open. -
Database Splitter becomes the Access Database command in the Database Tools ribbon's Move Data group. It divides a single-file Access .accdb application with application and data objects into a front-end .accdb file and a back-end Access database. This choice is disabled if you don't have a database open. Chapter 19, "Linking Access Front Ends to Access and Client/Server Tables" covers linking to tables in an Access back-end database. -
Switchboard Manager moves to a Switchboard Manager command in the Database Tools ribbon's Database Tools group. It 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 is also disabled if you don't have a database open.
-
Upsizing Wizard becomes the SQL Server (w) command in the Database Tools ribbon's Move Data group. It lets you move tables and queries from the current Access database to SQL Server 2005 [Express] and, optionally, change the .accdb file containing application objects to an Access Data Project (.adp) file. Chapter 19 describes how to use the Upsizing Wizard to link an .adddb front end to SQL Server tables. Chapter 22, "Upsizing Access Applications to Access Data Projects," covers creating ADPs. -
Make MDE File moves to the Make ACCDE command of the Database Tools ribbon's Database Tools Group. It creates a secure copy of the file, which prevents users from opening objects in Design view and viewing or changing VBA code.
Compacting and Repairing Databases
After you make numerous additions and changes to objects within a database file—especially 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 compact an Access file, you regain space only in 32KB increments.
To compact the current database, do the following:
- Open the database you want to compact.
- Choose Office, Manage, 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 where you were in the application before. Your compacted database is stored with the same name it had before you compacted it.
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 2007 database, you should compact and repair the database frequently. Access 2007 databases that are not compacted grow in size much more rapidly during modification than earlier versions.
Converting Earlier Database Formats to Access 2007 Format
To convert earlier Access version .mdb database or .mda library files created with Access 95 through Access 2003 to the new database format of Access 2007, open the file in Access 2007 and click Office, Save As, Access 2007 Database. Chapter 31, "Upgrading Access 200X Applications to Access 2007," covers this conversion process in detail.
If you encounter error messages when converting your Access 97 or 95 .mdb file to .accdb format, see the "Compile Errors in the Convert Database Process" topic of the "Troubleshooting" section near the end of the chapter.
Creating .accde Files
An .accde file is a special version of an Access .accdb file. In an .accde 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, .accde databases are used to create libraries of add-in wizards, deliver 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 2007 .accdb database to an .accde file by opening the file, clicking the Database Tools tab, and clicking the Make ACCDE button to open the Save As dialog. Navigate to the location for the .accde file and click Save to create and save the file. Then close the dialog.
Creating .accdr Runtime Files
An .accdr file is called a runtime Access file. You create a runtime file simply by changing FileName.accde to FileName.accdr. The .accdr version hides the ribbon and Navigation Pane, so you must provide a switchboard or equivalent to open forms and reports. The QAT is disabled and the Office button's gallery offers Print, Close Database, and Exit buttons only. Runtime Access files provide a minimalist UI, as illustrated by Figure 3.39.
)
Figure 3.39 Access Runtime (.accdr) files open with the ribbon and Navigation Pane hidden and disabled.
The problem with .accdr files is that users quickly discover they can simply change the file extension from .accdr to .accdb to regain lost design and navigation features.