Before You Get Started

Office Access 2007 makes it easy for you to change the design of your database, even when you already have data in your tables. You should, however, understand the potential impact of any changes you plan and take steps to ensure that you can recover your previous design if you make a mistake. Here are some things to consider before you make changes.

• Access 2007 does not automatically propagate changes that you make in tables to any queries, forms, reports, macros, or modules. You must make changes to dependent objects yourself, or configure Access to propagate the changes for you. To do so, click the Microsoft Office Button, click Access Options, and then in the Current Database category, select the Perform Name AutoCorrect check box. See “Setting Table Design Options” on page 39 for more details.

• You cannot change the data type of a field that is part of a relationship between tables. You must first delete the relationship and then change the field’s data type and redefine the relationship.

• You cannot change the definition of any table that you have open in a query, a form, or a report. You must close any objects that refer to the table you want to change before you open that table in Design view. If you give other users access to your database over a network, you won’t be able to change the table definition if someone else has the table (or a query or form based on the table) open.

Inside Out-Access Always Prompts You to Save Your Work 

Before saving any changes that permanently alter or delete data in your database, Access 2007 always prompts you for confirmation and gives you a chance to cancel the operation.

Making a Backup Copy

The safest way to make changes to the design of your database is to make a backup copy of the database before you begin. If you expect to make extensive changes to several tables in your database, you should also make a copy of the .accdb file that contains your database. You could use a utility such as Windows Explorer, but Access 2007 includes a handy feature for making backups easily. When you have the database open that you want to back up, click the Microsoft Office Button, click the Manage command, and then click Back Up Database as shown in Figure 5–1. Access offers to create a copy of your database with the current date appended to the file name.

Figure 5–1: The Back Up Database command creates a backup of your entire database file.

If you want to change a single table, you can easily make a backup copy of that table right in your database. Use the following procedure to copy any table-the structure and the data together.

1. Open the database containing the table you want to copy. If the database is already open, make sure the list of tables is showing in the Navigation Pane. Click the top of the Navigation Pane to open the Navigation Pane menu and click Object Type beneath Navigate To Category. Click the top of the Navigation Pane again and then click Tables under Filter By Group, as shown in Figure 5–2, to display only the tables contained in your database.

   
   Figure 5–2: Click Object Type and Tables on the Navigation Pane menu to display only the tables in your database.

2. Select the table you want to copy by clicking the table’s name or icon in the Navigation Pane. The table name will be highlighted.

3. Click the Copy command in the Clipboard group on the Home tab of the Ribbon as shown in Figure 5–3. This copies the entire table (structure and data) to the Clipboard.

   
   Figure 5–3: Click the Copy command to copy a table from the Tables list.

4. Click the Paste command in the Clipboard group on the Home tab of the Ribbon. Access opens the Paste Table As dialog box, shown in Figure 5–4. Type a new name for your table. (When naming a backup copy, you might simply add Backup and the date to the original table name, as shown in Figure 5–4.) The default option is to copy both the structure and the data. (You also have the option of copying only the table’s structure or of appending the data to another table.)

   
   Figure 5–4: Enter the new name for the copied table in the Paste Table As dialog box.

Checking Object Dependencies

If you’re just starting out and learning Office Access 2007 by reading this book through from the beginning, you probably haven’t built anything but tables yet. In Chapter 3 you learned that you can ask Access to show you what queries, forms, and reports are dependent on each table, but the Object Dependencies command in Access won’t provide very interesting results in a database with nothing but tables. You’ll find this tool invaluable after you have built dozens of objects and then need to make some changes to your tables.

As you learned in the previous chapter, you can select options to track and perform Name AutoCorrect for objects by clicking the Microsoft Office Button, clicking Access Options, and then selecting the check boxes for these features in the Current Database category. Access 2007 uses this AutoCorrect information not only to automatically correct names but also to provide you with detailed information about which objects depend on one another. If you’re about to make a change to a field in a table, wouldn’t it be good to know which queries, forms, and reports use that table before you make the change? The Perform Name AutoCorrect option will help you out if you have selected it, but it can’t always detect and fix field names when you have used them in an expression. You’ll learn more about creating expressions in Chapter 7, “Creating and Working with Simple Queries,” and in the chapters on using Microsoft Visual Basic later in this book.

If you would like to see object dependencies in action on your computer, open one of your own databases that contains tables, queries, forms, and reports, or open the Conrad Systems Contacts sample database (Contacts.accdb) that you installed from the companion CD. You can find out which other objects depend on a particular object (such as a table) by selecting the object that you’re planning to change in the Navigation Pane and then clicking Object Dependencies in the Show/Hide group on the Database Tools tab of the Ribbon. If you haven’t selected the Track Name AutoCorrect Info option, Access 2007 shows you the dialog box in Figure 5–5.

Figure 5–5: The Object Dependencies feature tells you it needs to turn on Track Name AutoCorrect Info and examine all objects in your database.

Click OK to turn on Track Name AutoCorrect Info-the Object Dependencies command will take a few seconds or minutes to examine all your objects depending on the number of objects you have in your database. Access shows you the result in the Object Dependencies pane as shown in Figure 5–6. At the bottom of the Object Dependencies pane in Figure 5–6, you can see a warning message that some objects were ignored. Access displays this message if there are macros or modules present in your database because macros and modules are not checked for object dependencies.

Figure 5–6: The Object Dependencies pane shows you the list of objects that depend on the object you selected in the Navigation Pane.

Notice that in many cases you will have to follow a chain of dependencies to find all the objects that might be affected by the change of a field name in a table. For example, in the Conrad Systems Contacts sample database, we use a query (qryContacts) rather than the table (tblContacts) to provide records to the form that edits contact information (frmContacts). If we were to scroll further down the Object Dependencies pane looking for forms dependent on tblContacts, we would not find the form frmContacts listed.

You can click the plus sign next to any object name to open an expanded list of dependent objects as we did with qryContacts in Figure 5–6. Notice that we find frmContacts listed there as a dependent object of qryContacts, which is ultimately dependent on the table we’re thinking about changing. When you find an object that you want to investigate further, you can click the object name in the Object Dependencies pane to open it in Design view.

As you can imagine, this command can make maintaining your application much easier. Even if you have selected the Perform Name AutoCorrect option, you can use this tool after you have modified your table to verify that the changes you expect were made.