Access 2010 analyzes your action query request and the data that you are about to change before it commits changes to your database. When it identifies errors, Access always gives you an opportunity to cancel the operation.
Access identifies (traps) four types of errors during the execution of an action query.
Duplicate primary keys. This type of error occurs if you attempt to append a record to a table or update a record in a table, which would result in a duplicate primary key or a duplicate of a unique index key value. Access will not update or append any rows that would create duplicates. For example, if the primary key of a contact event archive table is ContactID and ContactDateTime, Access won’t append a record that contains a ContactID and ContactDateTime value already in the table. Before attempting to append such rows, you might have to modify your append query not to select the duplicate rows.
Data conversion errors. This type of error occurs if you attempt to append data to an existing table and the data type of the receiving field does not match that of the sending field (and the data in the sending field cannot be converted to the appropriate data type). For example, this error will occur if you attempt to append a text field to an integer field and the text field contains either alphabetic characters or a number string that is too large for the integer field. You might also encounter a conversion error in an update query if you use a formula that attempts a calculation on a field that contains characters. For information on data conversions and potential limitations, see Table 5-3.
Locked records. This type of error can occur when you run a delete query or an update query on a table that you share with other users on a network. Access cannot update records that are in the process of being updated by some other user. You might want to wait and try again later when no one else is using the affected records to be sure that your update or deletion occurs. Even if you’re not sharing the data on a network, you can encounter this error if you have a form or another query open on the data you’re updating and have started to change some of the data.
Validation rule violations. If any of the rows being inserted or any of the rows being updated violates either a field validation rule or the table validation rule, Access notifies you of an error and does not insert or update any of the rows that fail the validation test. When you have a referential integrity rule defined, you cannot update or delete a row in a way that would violate the rule.
Another problem that can occur—although it isn’t an error—is that Access truncates data that is being appended to text or memo fields if the data does not fit. Access does not warn you when this happens. You must be sure (especially with append queries) that you have made the receiving text and memo fields large enough to store the incoming data.
Earlier in this chapter, you learned how to create an append query to copy old contact events to an archive table. What do you suppose would happen if you copied rows through December 31, 2010, forgot to delete them from the main table, and then later asked to copy rows through April 30, 2011? If you try this starting with an empty archive table in the ContactsDataCopy.accdb database, run qxmplArchiveContactEvents once, and then run it again with the same or later cutoff month, you’ll get an error dialog box similar to the one shown in Figure 11-30.
The dialog box in Figure 11-30 declares that 23 records won’t be inserted because of duplicate primary key values. Access didn’t find any data conversion errors, locking problems, or validation rule errors. Note that if some fields have data conversion problems, Access might still append the row but leave the field set to Null. When you see this dialog box, you can click Yes to proceed with the changes that Access can make without errors. You might find it difficult later, however, to track down all the records that were not updated successfully. Click No to cancel the append query.
To solve this problem, you can change the “select” part of the query to choose only the rows that haven’t already been inserted into the target table. Remember from Chapter 10 the technique that you used to find “unmatched” rows. You’ll apply that same technique to solve this problem.
Open the query that you built in the previous section (or qxmplArchiveContactEvents) in Design view. Add tblContactEventsHistory (the target table) to your query. Create join lines from the ContactID field in tblContactEvents to the same field in tblContactEventHistory. Do the same with ContactDateTime. Double-click each join line to open the Join Properties dialog box and choose the option to include all rows from tblContactEvents and the matching rows from tblContactEventsHistory. You must do this for each join line so that you end up with both lines pointing to tblContactEventsHistory. Include the ContactID field from tblContactEventsHistory in the design grid, clear the Append To box underneath it (you don’t want to try to insert ContactID twice), and place the criterion Is Null on the Criteria line. Your query should now look like Figure 11-31.
The result is that this query will select rows from tblContactEvents only if they don’t already exist in the archive table. You can now run this query as many times as you like. If all the rows you choose already exist, the query simply inserts no additional rows. You can find this query saved as qxmplArchiveContactEventsNoDuplicates in the sample database. There’s also a companion query, qxmplArchiveContactProductsNoDuplicates, to handle the archiving of contact product records.
At this point, you should have a reasonable understanding of how action queries can work for you. You can find some more examples of action queries in Article 2, “Understanding SQL,” on the companion CD. Now, it’s time to go on to building the user interface for your application with forms and reports.