Part III: More-Advanced Access Techniques
676
Alternatively, open the action query in design view, and use the Run command (the red exclama-
tion point) in the Results group on the Design ribbon tab. Unless you know exactly what an action
query will do, you may want to review the query’s design before running it directly from the
Navigation Pane, so the additional step of opening the query in Design view may protect your
database from issues as the query runs.Troubleshooting action queries
When you’re working with action queries, you need to be aware of several potential problems.
While you’re running the query, any of several messages may appear, including messages that
records were lost because of key violations or that records were locked during the execution of the
query. This section discusses some of these problems and how to avoid them.Data-type errors in appending and updating
If you try to enter a value that isn’t appropriate for the specified field, Access ignores the incorrect
values and inserts nulls into the fields. When you’re working with append queries, Access appends
the records, but fields might be blank because of data-type mismatches.Key violations in action queries
When you try to append records to another database that has a primary key, Access won’t append
records that contain duplicate primary key values.Access doesn’t allow updating a record by changing a primary key value to an existing value. You
can change a primary key value to another value under these conditions:l (^) The new primary key value doesn’t already exist.
l The field value you’re trying to change isn’t related to fields in other tables.
Access doesn’t enable you to delete a field on the one (parent) side of a one-to-many relationship
without first deleting the records from the many (child) side.
Access doesn’t enable you to append or update a field value that duplicates a value in a unique
index field. A unique index field is a field that has the Index property set to Yes (No Duplicates).
Record-locked fields in multiuser environments
Access won’t perform an action query on records locked by another user. When you’re performing
an update or append query, you can choose to continue and change all other values. But remember
this: If you enable Access to continue with an action query, you won’t be able to determine which
records were left unchanged!
Text fields
When you’re appending or updating to a text field that is smaller than the current field, Access
truncates text data that doesn’t fit in the new field. Access doesn’t warn you that it has truncated
the information.