Updating Table Data with Queries

Queries you create with the Unique Values property set to Yes to add the ANSI SQL DISTINCT modifier to the SQL statement aren't updatable. If you set the Unique Records property, instead of the Unique Values property, to Yes, some queries are updatable because Unique Records substitutes Jet SQL's DISTINCTROW modifier for DISTINCT.

T-SQL

T-SQL doesn't support Jet SQL's DISTINCTROW modifier and the rules that determine the updatability of SQL Server views and table-returning, user-defined functions differ from those of Jet. Chapter 21, "Moving from Jet Queries to Transact-SQL," covers updatability issues with SQL Server views and functions. Recordsets returned by SQL Server stored procedures aren't updatable.

Unique Records queries create Recordset objects of the updatable Dynaset type. You can't update table data with a query unless you see the tentative (blank) append record (with the asterisk in the select button) at the end of the query result table. The next few sections describe the conditions under which you can update a record of a table included in a query. The following sections also discuss how to use the Output Field Properties window to format query-data display and editing.

Note

You can't set both the Unique Values and Unique Records properties to Yes these choices are mutually exclusive. In Access 2003, the default setting of both the Unique Values and Unique Records properties is No.

Characteristics That Determine Whether You Can Update a Query

Adding new records to tables or updating existing data in tables included in a query is a definite advantage in some circumstances. Correcting data errors that appear when you run the query is especially tempting. Unfortunately, you can't append or update records in many queries that you create. The following properties of a query prevent you from appending and updating records:

• The Unique Values property is set to Yes in the Query Properties window.

• The Recordset Type property is set to Snapshot in the Query Properties window.

• Self-joins are used in the query.

• Jet SQL aggregate functions, such as Sum(), are employed in the query. Crosstab queries, for example, use SQL aggregate functions.

• The query has three or more tables with many-to-one-to-many relationships. Most queries with indirect relationships fall in this category.

• No primary-key field(s) with a unique (No Duplicates) index exist for the one table in a one-to-many relationship.

When designing a query to use as the basis of a form for data entry or editing, make sure that none of the preceding properties apply to the query.

Tip

You can't edit data returned by a query with three or more tables in Query Datasheet view, unless the query is one-to-many-to-many, but you can update values in other types of three-table queries in forms and data access pages (DAP) that are bound to the query. To make the query updatable with forms and DAP, set the Recordset Type property value to Dynaset (Inconsistent Updates).

If none of the preceding properties apply to the query or any table within the query, you can append records to and update fields of queries in the following:

• A single-table query

• Both tables in a one-to-one relationship

• The many table in a one-to-many relationship or the most-many table in a one-to-many-to-many relationship

• The one table in a one-to-many relationship if none of the fields of the many table appear in the query

Updating the one table in a one-to-many query is a special case in Access. To enable updates to this table, follow these steps:

1. Add to the query, the primary-key field or fields of the one table and additional fields to update. You don't need to add the primary-key field if its Jet data type is AutoNumber.

2. Add the foreign key field or fields of the many table that correspond to the key field or fields of the one table; this step is required to select the appropriate records for updating.

3. Add the criteria to select the records for updating to the fields chosen in step 2.

4. Click the Show box so that the many table fields don't appear in the query.

After following these steps, you can edit the nonkey fields of the one table. You can't, however, alter the values of key fields that have relationships with records in the many table, unless you specify Cascade Update Related Fields in the Relationships window's Edit Relationships dialog for the join. Otherwise, such a modification violates referential integrity.

By adding lookup fields to tables, you often can avoid writing one-to-many queries and precisely following the preceding rules to make such queries updatable. For example, the Orders table, which includes three lookup fields (CustomerID, EmployeeID, and ShipVia), is updatable. If you want to allow updates in Datasheet view (called browse updating), using lookup fields is a simpler approach than creating an updatable query. Most database developers, however, consider simple browse updating to be a poor practice because of the potential for inadvertent data-entry errors. As mentioned earlier, browse updating with lookup fields is especially prone to data-entry errors.

For more discussion of the browse-mode method and other alternatives, see "In the Real World Alternatives to Action Queries," p. 514.

Taking Advantage of Access's Row Fix-Up Feature

Access queries and SQL Server views have a row fix-up feature (called AutoLookup by Access) that fills in query data when you add a new record or change the value of the foreign key of a many-side record. To take advantage of row fix-up, your query must include the foreign-key value, not the primary-key value of the join.

Northwind Traders' Orders Qry is an example of a query that uses row fix-up. Orders Qry includes every field of the Customers and Orders tables, except the CustomerID field of the Customers table. To demonstrate row fix-up, do the following:

1. Open the Orders Qry in Datasheet view, and scroll to the tentative append record. Alternatively, press Ctrl+End, Home, and [da] to avoid the scrolling exercise.

2. Tab to the Customer column, open the lookup list, which is bound to the CustomerID field of the Customers table, and select a customer for a new order. The edited record symbol replaces the asterisk and the datasheet adds a new tentative append row.

3. Scroll the columns to the right until you reach the Address column, which displays the Address field of the Customers table. Row fix-up automatically enters data from the Customer table's record for the selected customer (see Figure 11.46).

   Figure 11.46. Row fix-up automatically adds data from the table on the one side of a one-to-many relationship when you add a new row. The first three columns of the datasheet are frozen to demonstrate row fix-up when adding a new record to the Orders table.

   

4. Press Esc to cancel the new record addition.

Row fix-up is more useful for forms that are bound to a query than for queries that update data in Datasheet view. Orders Qry is the data source for the sample Orders form. When you add a new order with this form, row fix-up automatically updates its customer data.

Formatting Data with the Query Field Properties Window

The display format of data in queries is inherited from the format of the data in the tables that underlie the query. You can override the table format by using the Format(ColumnName, FormatString) function to create a calculated field. In this case, however, the column isn't updatable.

Access provides an easier query column formatting method: the Field Properties window, which you can use to format the display of query data. You also can create an input mask to aid in updating the query data. To open the Field Properties window, place the cursor in the Field cell of the query column that you want to format, and then click the Properties button of the toolbar. Figure 11.47 shows the Field Properties window for the OrderDate column of the Orders Qry. Specifying formats in queries lets you alter the column's display format without affecting the display of table fields.

Figure 11.47. Access's Medium Date format (dd-mmm-yy) doesn't comply with Y2K requirements. To specify a four-year Medium Date format for all Windows operating systems, assign the dd-mmm-yyyy format.

By default, Access 2003's General Date, Long Date, and Short Date formats display four-digit years, which is required for Year 2000 (Y2K) conformance. (Four-digit years is the default for Windows XP and 2000.) You can alter the default Short Date or Long Date format in text boxes of the Date page of Control Panel's Customize Regional Options dialog. (Windows 2000's tool is called Regional Options.) Systemwide settings specify the General Date, Long Date, and Short Date formats, but don't affect the Medium Date style. To obtain a four-digit year display with Medium Date format, you must type the Format descriptor string dd-mmm-yyyy in the Format text box. Most sample tables in Northwind.mdb have the custom dd-mmm-yyyy format applied.

Note

The format symbol for month in the [Customize] Regional Options dialog is "M", not "m", which is the systemwide symbol for minutes. Access and VBA use "n" for minutes.

Tip

Always use the default Short Date and Long Date systemwide formats. Don't depend on users to change their default formats. If you want to specify two-digit day and month values, for example, use a custom date format, such as mm/dd/yyyy.

The Field Properties window displays the following subset of the properties that apply to a query's fields:

• Description lets you enter the text to appear in the status bar when the user selects the field in Datasheet view.

• Format lets you control the appearance of the data in Datasheet view, such as Short Date.

• Input Mask lets you specify the format for entering data, such as 99/99/0000. (To create an input mask that is appropriate for the field data type, click the ellipsis button to open the Input Mask Wizard.)

For more information on the Input Mask Wizard and a listing of placeholders, see "Using Input Masks," p. 173.

• Caption lets you change the query column heading, such as Received, for the Order Date column.

• Smart tags are a new feature of Access 2003 that enable links to Web-based resources and perform other operations, such as propagating changes to field or column properties to dependent forms and reports.

To learn more about smart tags, see "Working with Object Dependencies and Access Smart Tags," p. 200.

Each of the preceding query properties follows the rules described in Chapter 5 for setting table field properties. Adding a value (Received) for the Caption property of a query against the Orders table is the equivalent of adding a column alias by typing Received: as a prefix in the OrderDate column's Field cell. Adding a Caption property value, however, doesn't change the SQL statement for the query. The value of the Input Mask property need not correspond exactly to the value of the Format property, but input mask characters don't appear if you try to use a Short Date mask with a Medium Date format you apply in the query.

Tip

Add captions to queries, not tables. Table Datasheet view should display field names rather than captions to conform to good database design principles. Unfortunately, the tables in Northwind.mdb don't conform to this recommendation.

For example, the Received (OrderDate) column in Figure 11.48, which shows the effect of setting the property values shown in the preceding list, has a single-digit (no leading zero) month and day for the Short Date display format, which overrides the mm/dd/yyyy format of the table field. The input mask (99/99/0000;0;_) permits updating with one-digit or two-digit months and days. Adding or editing a single-digit or two-digit entry gives the same result. Most typists prefer to enter a consistent number of digits in a date field.

Figure 11.48. This query uses m/d/yyyy display format and a 90/90/0000 input mask to allow month and date entries as single- or two-digit values.

If your query has tables linked to dBASE, FoxPro, or other non-Access tables and you can't update records in or add records to the query result set, see "Queries with Linked Tables Aren't Updatable" in the "Troubleshooting" section near the end of this chapter.