Update the data in a database

This article explains how to update existing data. Microsoft Office Access 2007 provides a number of tools for updating existing records, including datasheets, forms, queries, find-and-replace, and the new Data Collection feature.

As you proceed, remember that updating data is not the same process as entering new data. For information about entering new data in a database, see the article Add one or more records to a database.


Top of Page

How database design affects updating

Read this section if you are new to Access or unfamiliar with the concepts behind relational databases. Large updates become much easier to perform when you understand some of the basic principles of database design.
An Access database is not a file in the same sense as a Microsoft Office Word 2007 document or a Microsoft Office PowerPoint 2007 slide deck. Instead, a typical Access database is a a collection of tables, plus a set of objects built around those tables — forms, reports, queries, and so on.
In addition, those objects must adhere to a set of design principles or the database will either work poorly or fail altogether. In turn, those design principles affect how you enter data. Remember these facts about database objects and design as you proceed.
With a few exceptions (such as a type of list called a value list), Access stores all data in one or more tables. The number of tables depends on the design and complexity of the database. Although you may view or update the data in a form, a report, or in the results returned by a query, Access stores the data only in tables.
Each table should store data for a single issue, category, or purpose. For example, a table of business contact information should not contain sales information. If it does, finding the correct information can become difficult, if not impossible.
Each of the fields in a table should accept only one type of data. For example, you should not store notes in a field set to accept numbers. If you try to enter text in such a field, Access displays an error message.
With one exception, the fields in a record should accept only one value. For example, a properly designed database will prevent you from entering more than one address in an address field. This is in contrast to Microsoft Office Excel 2007, which, by default, allows you to enter any number of names, addresses, or images in a single cell unless you set that cell to accept limited types of data.
However, Office Access 2007 also provides a new feature called a multivalued field. You use multivalued fields to attach multiple pieces of data to a single record, and to create lists that accept multiple values. For example, you can attach a text file, a Office PowerPoint 2007 slide deck, and any number of images to a record in your database. You can also create a list of names and select as many of those names as needed. The use of multivalued fields may seem to break the rules of database design because you can store more than one piece of data per table field, but it actually doesn't, because Access enforces the rules "behind the scenes," by storing the data in special, hidden tables.
The following links take you to articles that provide more information about the subjects and features discussed in this section.
Database design basics —This article explains fundamental concepts such as planning a database, designing the data, and normalization — the process of splitting your data into related tables and eliminating redundant data.
Create tables in a database —This article explains how to create tables, add primary keys (fields that uniquely identify each row or record in the table), and how to set data types and table properties.
Attach files and graphics to the records in your database —This article explains how to attach one or more pieces of data to an Attachment field.
Use a list that stores multiple values and Guide to multivalued fields —These articles explain how to use check box lists and check box drop-down lists to store more than one value.

Top of Page

How data types affect updating.
Read this section if you are new to Access or unfamiliar with the concepts behind relational databases. Large updates become much easier to perform when you understand some of the basic principles of database design.
When you design a database table, you select a data type for each of the fields in that table, a process that helps ensure more accurate data entry. For example, suppose you specify the Number data type for a field because you need to calculate sales figures. If someone tries to enter text in that field, Access displays an error message and will not let that user save the changed record, a step that helps protect your figures.
Access now provides two ways to view the data types for a table field. You can use the commands on the Datasheet tab, or you can open the table in Design view. The following sets of steps explain how to use both techniques.

Top of Page

How table field properties affect updating

Read this section if you are new to Access or unfamiliar with the concepts behind relational databases. You cannot perform large updates successfully unless you understand how the properties set for table fields affect updating.

When you design a database, you typically start by designing one or more tables. You decide what kinds of data each table will contain, you set primary keys — a field that uniquely identifies each record (row) — for each table, and you create the relationships among the tables.

As part of that process, you set properties for the fields in each table. For example, you can set a Text field to accept no more than 50 characters, and you can set a Number field to accept only currency values.

 Note    You can set most field properties by using Design view. However, you can also set some properties by using commands in the groups on the Ribbon, part of the Microsoft Office Fluent user interface. For example, you can set visual formats for Text and Memo fields by using the commands in the Font group on the Home tab. For more information about using those commands, see the article Format data in rows and columns.

Top of Page

Use a form to update data

You use a form to update small amounts of data. In this case, "small" means any number of records that you want to update manually. Forms can provide an easier, faster, and more accurate way to edit and update small amounts data.

How you use a form to edit data depends on the design of the form. Forms can contain any number of controls such as lists, text boxes, buttons, and datasheets — grids that look like Excel worksheets. In turn, each of the controls on the form either reads data from or writes data to an underlying table field. What you do with a given control depends on the data type set for the underlying table field, any properties set for that field, and possibly on several properties that the database designer sets for each control. For more information about how data types and field properties affect data entry, see How data types affect the way you enter data and How table field properties affect the way you enter data, earlier in this article.

The following sections explain how to use the most common data entry controls. If you have questions about your specific database, contact your system administrator or the database designer.


Top of Page