Every template that is included with Access 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
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 Access 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.
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 Click to Add column heading. You can also add a field to table in Design view.
To learn more about adding a field to a table in Datasheet view, see the article Add or delete a column in a datasheet.
To learn more about adding a field to a table in Design view, see the article Create a table and add fields.
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.
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 article Create, edit or delete a relationship.
For step-by-step instructions on how to delete fields from tables, see the article Delete a field.
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. Access provides the Name AutoCorrect feature, which helps propagate the name changes. By default, Name AutoCorrect is turned on for all new databases in Access. However, if it is turned off, you can do the following to turn it on.
Turn on the Name AutoCorrect option
-
Click File > Options.Â
-
In the Access Options dialog box, in the left pane, click Current Database.
-
Under Name AutoCorrect Options, select the Track name AutoCorrect info check box, and then select the Perform name AutoCorrect check box.
-
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.
-
Click OK.
-
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
-
In the Navigation Pane, double-click the table in which you want to rename the field.
The table is opened in Datasheet view.
-
Right-click the column heading for the field that you want to rename, and then click Rename Field on the shortcut menu.
-
Type the new name for the field and then press ENTER.
Rename a field in Design view
-
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.
-
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.
-
Edit the text to rename the field.
-
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.
-
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.
-
Type the new name and then press ENTER.
-
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 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:
-
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.
-
Click the cell in the Field Name column for the field whose Caption property you want to set.
-
In the bottom section, under Field Properties, on the General tab, click Caption.
-
Type a new caption for the field.
-
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.
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.
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.
For a full list of the types of controls you can use in forms and reports, see the article Introduction to controls.
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.
Open the property sheet
-
On the Design 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 Design tab, in the Tools 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 Add a field to a form or report, Create a form in Access, and Create a simple report.
Add a field that stores documents, files, or images
You can use Access 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
-
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.
-
In the Field Name column, click the first blank row and type a name for the new field.
-
Click the adjacent cell in the Data Type column, and then select Attachment in the list.
-
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.
-
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
-
In the Navigation Pane, double-click the table to which you want to add the attachment.
The table is opened in Datasheet view.
-
In the table, double-click the attachment field.
The Attachments dialog box appears.
-
Click Add.
The Choose File dialog box appears.
-
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.
-
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.
-
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.
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
-
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.
-
Click the field whose display format you want to set.
-
On the Fields tab, in the Formatting group, click the arrow in the drop-down list next to Format, and then select a format.
The field is displayed with the new display format.
Set the display format in Design view
-
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.
-
Click the field whose display format you want to set.
-
Under Field Properties, on the General tab, click the Format box.
-
Click the drop-down list and select a display format.
-
To save your changes, click Save on the Quick Access Toolbar.
