Chapter 4: Selecting Data with Queries
157
In Access, the records in your tables might not always be updateable. Table 4.1 shows when a field
in a table is updateable. As Table 4.1 shows, queries based on one-to-many relationships are
updateable in both tables (depending on how the query was designed).TABLE 4.1
Rules for Updating Queries
Type of Query or Field Updateable Comments
One table Yes
One-to-one relationship Yes
Results contains Memo field Yes Memo field updateable.
Results contain a hyperlink Yes Hyperlink updateable.
Results contain an OLE object Yes OLE object updateable.
One-to-many relationship Usually Restrictions based on design methodology (see
text).
Many-to-one-to-many relationship No Can update data in a form or data access page if
Record Type = Recordset.
Two or more tables with no join line No Must have a join to determine updateability.
Crosstab No Creates a snapshot of the data.
Totals query (Sum, Avg, and so on) No Works with grouped data creating a snapshot.
Unique Value property is Yes No Shows unique records only in a snapshot.
SQL-specific queries No Union and pass-through work with ODBC data.
Calculated field No Will recalculate automatically.
Read-only fields No If opened read-only or on read-only drive
(CD-ROM).
Permissions denied No Insert, replace, or delete are not granted.
ODBC tables with no unique identifier No A unique identifier must exist.
Paradox table with no primary key No A primary key file must exist.
Locked by another user No Can’t be updated while a field is locked by
another.Overcoming query limitations
Table 4.1 shows that there are times when queries and fields in tables are not updateable. As a gen-
eral rule, any query that performs aggregate operations or uses an Open DataBase Connectivity
(ODBC) data source is not updateable; most other queries can be updated. When your query has
more than one table and some of the tables have a one-to-many relationship, some fields might not
be updateable (depending on the design of the query).