databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Action Queries

Creating Action Queries - Update, Append and Delete Queries:

Most Microsoft Access database users will use database queries to request or question information from one or more of your database tables. In Microsoft Access certain queries can also be used to change data as well as display or retrieve it.

These queries, defined as Microsoft Action queries, will allow the user to update data (Update Query), delete data (Delete Query) or add data from one database table to another (Append Query).

An action query will be considered a normal select query which is then given the option to perform a duty against a specified section or group of records in the database. Once the specified action is assigned (update, delete or append) this will then define the action that will be taken against the selected records. An action query is an efficient tool for making bulk data modifications against your database tables.

The uses of an Action Query

A Microsoft Action Query can perform the following types of tasks:

Example usage of Microsoft Access action queries

  • You may wish to update a complete product price list to represent a 10 percent price increase across all of these products. Rather than having to manually alter all of the prices for these products, you can save a good deal of time by creating a query to automatically update these for you.
  • You store a large amount of daily records, but wish to archive outdated records into a history table. A record might be archived if it is over two years old. You would need to create an Append query to move the data to an Archive table, and then run a Delete query against the Original table to remove these now that you have an historical record stored elsewhere.

Note: You should always exercise caution when performing Microsoft Access action queries as these queries will change, add or delete data from your database. It would therefore be a good idea to observe the following precautions:

  • Always create a backup of at least your table(s) before performing an action query. If possible ensure that you have a backup of the complete database.
  • Always create and view the query as a standard SELECT query and view the data before changing the query type to an action query.

Action Queries - The Process

As an action query is irreversible, use the following guidelines to ensure that the process runs as smooth as possible:

  • Create the action query, specifying the required fields and applying any required criteria.
  • View the data selected in the action query by clicking on the Datasheet button on the toolbar (as opposed to using the run (!) button.
  • Once you are happy that the selected data is correct, run the action query by clicking on the run (!) button on the toolbar.
  • Check that the changes made to the data in your tables is correct.

Action Queries - Reversing the action performed

The results of an action query can not be reversed, it changes or copies the data in underlying tables. It is therefore strongly advised to check things before performing and always backup data tables.

Troubleshoot Action Queries

Because of the nature of action queries in Microsoft Access and the fact that they will change or delete data, you will need to be aware of any potential problems that may arise. When you run this type of query, you may at times be presented with possible error messages, including messages informing you that records have been lost due to key violations or records being locked whilst executing the query. Below details some of the problems and ideas on how to avoid these issues arising:

Key Violations in action queries

If you attempt to append data to another database that contains a Primary Key, Microsoft Access will not append records that contain the same Primary Key value.

Microsoft Access will not allow you to delete a record from the Primary (Parent) table in the one side of a one-to-many relationship without first deleting the child record(s) from the many side of the relationship.

Access will not allow you to update a record or append a record that will duplicate values in a unique index field

Data Type errors when appending and updating with action queries

If you try to update or append data that is not appropriate for a specified field in Microsoft Access, it will simply not enter the value; it will ignore the incorrect value and convert the fields to Null values. In an Append query, the records will be appended, but certain fields may be left blank.

Appending to Text fields

If data is appended to a text field, and the field size is set to smaller than that of the data that is being appended, Microsoft Access will truncate any data that does not fit the new field. You will not be warned that this data has been truncated.