Learn how to modify your new database

Every template that is included with Microsoft Office Access 2007 is a complete tracking application that contains predefined tables, forms, reports, queries, macros, and relationships. These templates are designed to be immediately useful out-of-the-box, so that you can create a new database that is based on a template and get up and running quickly. However, there might be times when you want to modify your new database — for example, to add or rename a field, or change a report.

This article explains how to perform the most common modifications you might want to apply to a database that was created from a template. See the links to articles for more in-depth information about specific areas.

What do you want to do?

Learn the basics of tables and fields

Add a field to a table

Delete a field from a table

Rename a field or table

Add a field to a form or report

Add a field that stores documents, files, or images

Change how a field appears

Learn the basics of tables and fields

When you create a database, you store your data in tables — subject-based lists of rows and columns. You store the information items that you want to track in fields (also called columns). For example, in a Contacts table, you might create fields for Last Name, First Name, Telephone Number, and Address. For a Products table, you might create fields for Product Name, Product ID, and Price.

It is important that you choose fields carefully. For example, it is usually a bad idea to create a field to store a calculated value. In most cases, you can have Office Access 2007 calculate the value when it is needed. When you choose fields, try to store information in its smallest useful parts. For example, instead of a full name in a single field, consider storing the first and last names separately. Generally, if you need to report, sort, search, or perform a calculation on an item of information, put it in a field by itself.

For more information about designing a database and choosing fields, see the article Database design basics.

A field has certain defining characteristics. For example, every field has a name that uniquely identifies the field within a table. A field also has a data type that is chosen to match the information to be stored. The data type determines the values that can be stored and the operations that can be performed on those values, as well as how much storage space to set aside for each value. Every field also has an associated group of settings called properties that define the appearance or behavior characteristics of the field. For example, the Format property defines a field's display layout — that is, how it should appear when displayed.

Top of Page

Add a field to a table

You can easily add a field to a table in Datasheet view. However, you can also add a field to a table in Design view. In Datasheet view, you add the field by typing some data into the cell below the Add New Field column heading.

Datasheet in Access with Add New Field column

1. A new, blank field.

Add a field in Datasheet view

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, double-click the table in which you want to add the new field.

    The table is opened in Datasheet view.

  4. Type your data in the cell below the Add New Field column heading.

    If the table already has many fields, you might have to scroll to the right to see the column that has the Add New Field column heading.

When you enter data in the new column, Office Access 2007 uses the information that you type to recognize the appropriate data type for the field. For example, if you type a date in the column, such as 1/1/2007, Office Access 2007 recognizes the information that you entered as a date and sets the data type for the field to Date/Time. If Access doesn't have enough information from what you enter to determine the data type, the data type is set to Text.

If you want to explicitly set the data type and format for a field, which overrides the choice that Office Access 2007 makes, you can do so by clicking the commands in the Data Type & Formatting group on the Datasheet tab.

Explicitly set the data type

  • On the Datasheet tab, in the Data Type & Formatting group, click the arrow in the drop-down list next to Data Type, and then select a data type. Access Ribbon Image of Data Type and Formatting group

  • Click the data type that you want.

Explicitly set the format

  1. On the Datasheet tab, in the Data Type & Formatting group, click the arrow in the drop-down list next to Format, and then select a format. Access Ribbon Image of Data Type and Formatting group

  2. Click the format that you want.

When you add a field by typing information into the cell below the Add New Field heading, Office Access 2007 automatically assigns a name to the field. These names start with Field1 for the first field, Field2 for the second field, and so on. It is a good practice to use more descriptive field names. You can rename the field by right-clicking its heading and then clicking Rename Column on the shortcut menu.

In addition to adding a field in Datasheet view, you can add a field in Design view. Because Datasheet view lets you set only the most common field properties, you might find that you need to use Design view to set a property that is not available in Datasheet view.

Add a field in Design view

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, right-click the table in which you want to add a new field, and then click Design View on the shortcut menu.

    The table is opened in Design view.

  4. In the Field Name column, click the first blank row and type a name for the new field.

  5. Click the adjacent cell in the Data Type column, and then select a data type from the list.

  6. To save your changes, click Save on the Quick Access Toolbar.

For more information about how to add a new field, see the article Insert, add, or create a new field in a table.

Note: When you add a new field to a table, the field is not automatically added to your existing forms and reports. You must manually add the field to those forms and reports in order for it to appear in them.

Top of Page

Delete a field from a table

If possible, you should avoid deleting a field from a database that was generated from one of the supplied templates — it is likely that the field is employed in other database objects, such as forms and reports. Thus, deleting the field will create consequences when you attempt to use the other database objects that employ the field — the database objects will not work as expected. You will have to remove any references to the field from all of the objects that employ it in order for those other objects to work correctly.

When you decide that you must delete a field from a database that was generated from a template, you can do so in either Datasheet view or Design view. Remember that if other database objects reference the deleted field, you must modify those other objects to remove the reference. For example, if a report includes a control that is bound to the deleted field and you run the report, an error message appears, because Access cannot find the data for the field.

When you delete a field, you permanently delete all of the information stored in that field. For that reason, you should use caution when deleting fields, and you should create a backup copy of your database before you delete the field.

Before you can delete a field, you must ensure that it doesn't participate in any table relationships. If you try to delete a field for which relationships exist, Access warns you that you must first delete the relationships. For step-by-step instructions on how to delete a table relationship, see the section Delete a table relationship.

Delete a field in Datasheet view

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, double-click the table from which you want to delete the field.

    The table is opened in Datasheet view.

  4. Select the field (the column) that you want to delete.

    Tip: To select the field (the column), click the field selector (the column heading).

  5. Press DELETE.

    –or–

    On the Datasheet tab, in the Fields & Columns group, click Delete.

Note: You cannot delete a field that is part of a table's primary key in Datasheet view. To delete a primary key field, you must use Design view.

For more information about primary keys, see the article Add, set, change, or remove the primary key.

Delete a field in Design view

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, right-click the table from which you want to delete the field, and then click Design View on the shortcut menu.

    The table is opened in Design view.

  4. Select the field (the row) that you want to delete.

    Tip: To select the row, click the row selector.

  5. Press DELETE.

    –or–

    On the Design tab, in the Tools group, click Delete Rows.

  6. To save your changes, click Save on the Quick Access Toolbar.

Before you can delete a field, you must ensure that it doesn't participate in any table relationships. If you try to delete a field for which relationships exist, Access warns you that you must first delete the relationships. To delete a table relationship, do the following.

Delete a table relationship

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. If the tables that participate in the table relationship are open, close them. You cannot delete a table relationship between open tables.

  4. On the Database Tools tab, in the Show/Hide group, click Relationships.

  5. If the tables that participate in the table relationship are not visible, do the following:

    1. On the Design tab, in the Show/Hide group, click Table Names.

    2. In the Show Table dialog box, select the tables to add, click Add, and then click Close.

  6. Click the relationship line for the table relationship that you want to delete (the line appears bold when it is selected), and then press DELETE.

Top of Page

Rename a field or table

If possible, you should avoid renaming a field or table in a database that was generated from one of the supplied templates — it is likely that the field or table is employed in other database objects, such as forms and reports. Thus, renaming the field or table can create consequences when you attempt to use the other database objects that employ the field or table. The other objects may not work as expected if they still reference the old name. For the other objects to work correctly, the old name must be changed to the new name. If the Perform name AutoCorrect check box is selected in the Current Database category of the Access Options dialog box, much of this work will be done for you automatically.

You might want to change the text that appears in a column heading in Datasheet view. You can do so without having to rename the field. If you want to change only the text in the column heading, and you don't want to rename the field, see the section Change the text that appears in a column heading.

When necessary, you can rename a table in the Navigation Pane, or rename a field in either Datasheet view or Design view. Before you do so, you should consider turning on the Name AutoCorrect option, if it is not already turned on.

Turn on the Name AutoCorrect option

When you rename a database object, such as a field, table, form, or report, you usually want the name change to be propagated throughout the database. Otherwise, objects that reference the old name will not work as expected. Office Access 2007 provides the Name AutoCorrect feature, which helps propagate the name changes. By default, Name AutoCorrect is turned on for all new databases in Office Access 2007. However, if it is turned off, you can do the following to turn it on.

Turn on the Name AutoCorrect option

  1. Click the Microsoft Office Button Office button image , and then click Access Options.

  2. In the Access Options dialog box, in the left pane, click Current Database.

  3. Under Name AutoCorrect Options, select the Track name AutoCorrect info check box, and then select the Perform name AutoCorrect check box.

  4. If you want to keep a table that logs each change that is performed by Name AutoCorrect, select the Log name AutoCorrect changes check box.

  5. Click OK.

  6. To save your changes, click Save on the Quick Access Toolbar.

Note: The Name AutoCorrect feature is not the same as the AutoCorrect feature. The Name AutoCorrect feature corrects references between objects. The AutoCorrect feature corrects frequently misspelled words or phrases.

Rename a field in Datasheet view

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, double-click the table in which you want to rename the field.

    The table is opened in Datasheet view.

  4. Right-click the column heading for the field that you want to rename, and then click Rename Column on the shortcut menu.

  5. Type the new name for the field and then press ENTER.

Rename a field in Design view

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, right-click the table in which you want to rename the field, and then click Design View on the shortcut menu.

    The table is opened in Design view.

  4. Click the cell in the Field Name column for the field that you want to rename.

    Tip: To select the entire field name, point just to the left of the first character in the name until the pointer changes to an arrow, and then click.

  5. Edit the text to rename the field.

  6. To save your changes, click Save on the Quick Access Toolbar.

Rename a table

You can rename a table and most other database objects directly from the Navigation Pane.

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, right-click the table that you want to rename ,and then click Rename on the shortcut menu.

    Note: You must close all open objects that reference the table before you can rename it.

  4. Type the new name and then press ENTER.

  5. To save your changes, click Save on the Quick Access Toolbar.

Change the text that appears in a column heading

A caption is a title or heading that you can assign to a field by setting the field's Caption property. The caption is independent of the field name — every field has a field name and can also have a caption. For example, you might want your field name to be a single word without embedded spaces. You can then use the Caption property to create a more descriptive name that includes spaces. Access displays the caption in place of the field name in Datasheet view and in labels and headings in queries, forms, and reports.

If you don't supply text for the Caption property, the field name is used by by default.

If you want to change the name that appears in the heading for a column in Datasheet view, you can do so without changing the field name by setting the field's Caption property. To change a field's Caption property, do the following:

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, right-click the table in which you want to change the caption, and then click Design View on the shortcut menu.

    The table is opened in Design view.

  4. Click the cell in the Field Name column for the field whose Caption property you want to set.

  5. In the bottom section, under Field Properties, on the General tab, click Caption.

  6. Type a new caption for the field.

  7. To save your changes, click Save on the Quick Access Toolbar.

The next time you open the table in Datasheet view, the caption instead of the field name will appear in the column heading.

Top of Page

Add a field to a form or report

When you create a database that you based on a template, your database will contain a number of built-in, predesigned forms and reports that you can work with immediately. You might find, however, that you want to add an additional field to a form or report. When you add a field to a form or report, you create what is called a control.

Introducing controls

Controls are objects that display data, perform actions, and let you view and work with information that enhances the user interface, such as labels and images. Controls can be bound, unbound, and calculated.

  • Bound control    This is a control whose source of data is a field in a table or query. You use bound controls to display values from fields in your database. The values can be text, dates, numbers, Yes/No values, pictures, or graphs. A text box is the most common type of bound control. For example, a text box on a form that displays an employee's last name might get this information from the Last Name field in the Employees table.

  • Unbound control    This type of control doesn't have a source of data (a field or expression). You use unbound controls to display information, lines, rectangles, and pictures. For example, a label that displays the title of a form is an unbound control.

  • Calculated control    This is a control whose source of data is an expression rather than a field. You specify the value that you want in the control by defining an expression as the source of data for the control. An expression is a combination of operators (such as = and + ), control names, field names, functions that return a single value, and constant values. For example, the following expression calculates the price of an item with a 25 percent discount by multiplying the value in the Unit Price field by a constant value (0.75).

= [Unit Price] * 0.75

An expression can use data from a field in the form's underlying table or query, or data from a control on the form. For more information about expressions, see the links in the See Also section of this article.

A text box can be a bound control, an unbound control, or a calculated control. When you create a form that uses bound, unbound, and calculated controls, it is probably most efficient to add and arrange all the bound controls first, especially if they make up the majority of the controls on the form. You can then add the unbound and calculated controls that complete the design by using the tools in the Controls group on the Design tab in Design view.

You bind a text box control to a field by identifying the field from which the control gets its data. You can create a control that is bound to the selected field by dragging the field from the Field List pane to the form. The Field List pane displays the fields of the form's underlying table or query. To display the Field List pane, on the Design tab, in the Tools group, click Add Existing Fields. By default, when you drag a field from the Field List pane to the design grid of a form, Access inserts a text box.

Alternatively, you can bind a field to a control by typing the field name in the control itself or in the box for the Control Source value in the control's property sheet. The property sheet defines the characteristics of the control, such as its name, the source of its data, and its format.

Using the Field List pane is the best way to create a bound text box for two reasons:

  • A bound text box has an attached label, and the label takes the name of the field (or the caption defined for that field in the underlying table or query) as its caption by default, so you don't have to type the caption yourself.

  • A bound text box inherits many of the same settings as the field in the underlying table or query (such as for the Format, DecimalPlaces, and InputMask properties). Therefore, you can be sure that these properties for the field remain the same whenever you create a text box that is bound to that field.

If you already created an unbound control and want to bind it to a field, set the control's ControlSource property to the name of the field.

Add a field to a form or report in Layout view

After you create a form or report, you can easily fine-tune its design by working in Layout view. By using the actual live data as your guide, you can adjust the field widths and rearrange the fields. You can place new fields on the form or report and set the properties for the form or report and their controls.

To switch to Layout view, in the Navigation Pane, right-click the form or report, and then click Layout View on the shortcut menu. Alternatively, you can click the Layout View button on the Access status bar, or you can right-click the document tab for the form or report and then click Layout View on the shortcut menu.

Switch to Layout view

  • In the Navigation Pane, right-click the form or report and then click Layout View on the shortcut menu.

    -or-

    Click the Layout View button on the Access status bar.

    -or-

    Right-click the document tab for the form or report, and then click Layout View on the shortcut menu.

The form or report appears in Layout view.

Open the property sheet

  • On the Arrange tab, in the Tools group, click Property Sheet.

    Keyboard shortcut  Press F4.

You can use the Field List pane to add fields from the underlying table or query to your design. To display the Field List pane, on the Format tab, in the Controls group, click Add Existing Fields. You can then drag fields directly from the Field List pane onto your design.

Display the Field List pane

  • On the Format tab, in the Controls group, click Add Existing Fields.

Add a field from the Field List pane

  • To add a single field, drag the field from the Field List pane to the section where you want it displayed on the form or report.

  • To add several fields at once, hold down CTRL and click the fields that you want. Drag the selected fields onto the form or report.

When you drop the fields onto a section, Access creates a bound text box control for each field and automatically places a label control beside each field.

For more information about forms and reports, see the articles Create a form by using the Form tool and Create a simple report.

Top of Page

Add a field that stores documents, files, or images

You can use Office Access 2007 to add a field with the Attachment data type to store one or more documents, files, or images. You can use an attachment field to store several files in a single field. You can even store more than one type of file in that field. For example, in an employment candidate database, you can attach one or more resumes, in addition to a photo, to the record for each contact.

Add an attachment field to a table

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, right-click the table in which you want to add the field, and then click Design View on the shortcut menu.

    The table is opened in Design view.

  4. In the Field Name column, click the first blank row and type a name for the new field.

  5. Click the adjacent cell in the Data Type column, and then select Attachment in the list.

  6. To save your changes, click Save on the Quick Access Toolbar.

    Access might display a message that says you cannot undo your changes after you save the table. This means that you cannot convert the field to another data type, but you can delete the field, if you think you made an error.

  7. Click Yes to confirm the change.

After you add the Attachment field to the table, you can use the Attachments dialog box to add documents, files, or images to a record.

Add an attachment to a field

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, double-click the table to which you want to add the attachment.

    The table is opened in Datasheet view.

  4. In the table, double-click the attachment field.

    The Attachments dialog box appears.

  5. Click Add.

    The Choose File dialog box appears.

  6. Use the Look in list to browse to the file or files that you want to attach to the record, select the file or files, and then click Open.

    Note that you can select multiple files of any supported data type.

  7. In the Attachments dialog box, click OK to add the files to your table.

    Access adds the files to the field and increments the number that indicates the attachments accordingly.

  8. Repeat these steps as needed to add files to the current field or other fields in your table.

For more information about attachment fields, see the article Attach files and graphics to the records in your database.

Top of Page

Change how a field appears

You can customize how a field appears when it is displayed. For example, you can set display formats to use throughout your database, such as in forms and reports, or when a table is opened in Datasheet view. To set a display format, you set a field's Format property. The field's Format property is then inherited automatically by new forms and reports that you create.

Set the display format in Datasheet view

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, double-click the table that contains the field whose format you want to set.
    The table is opened in Datasheet view.

  4. Click the field whose display format you want to set.

  5. On the Datasheet tab, in the Data Type & Formatting group, click the arrow in the drop-down list next to Format, and then select a format. Access Ribbon Image of Data Type and Formatting group

    The field is displayed with the new display format.

Set the display format in Design view

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the database.

  3. In the Navigation Pane, right-click the table that contains the field whose display format you want to change, and then click Design View on the shortcut menu.

    The table is opened in Design view.

  4. Click the field whose display format you want to set.

  5. Under Field Properties, on the General tab, click the Format box.

  6. Click the drop-down list and select a display format.

  7. To save your changes, click Save on the Quick Access Toolbar.

For more information about how to change the appearance of a field, see the article Format data in rows and columns.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×