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.
- View data types by using commands on the Datasheet tab
- In the Navigation Pane, double-click the table that you want to use.
- Click the field that you want to investigate.
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