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.
A Microsoft Action Query can perform the following types of tasks:
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:
As an action query is irreversible, use the following guidelines to ensure that the process runs as smooth as possible:
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.
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:
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
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.
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.