About updating data in an Access project

General guidelines for updating data

The following general guidelines apply when you are updating data in a Microsoft Access project:

• Base tables in a query must have a primary key defined. When you first create a table in Database Designer or Table Designer, Access prompts you to create a primary key.
• To update any field (including the primary key field) in a single table or base table of a join, you must have a unique field or combination of fields defined as a primary key constraint, a unique constraint, or any unique index on that table.
• To update data in a query, you must have UPDATE, INSERT and DELETE permission on the base table(s) referenced in the query, as well as SELECT permission on a view or EXECUTE permission on a stored procedure or user-defined function.

When you can update data

In general, you can update data under the following circumstances:

• In general, views can be updated subject to the query restrictions described in the next section below. However, in SQL Server 2000 databases, you can add the WITH VIEW_METADATA clause to the CREATE VIEW SQL statement (Select the Update using view rules check box in the View properties page). This clause allows updates to partitioned views, with certain restrictions, and the use of INSTEAD OF triggers to make the view updateable. For more information on updateable views, see the CREATE VIEW TRANSACT-SQL statement in the SQL Server documentation.

• A query based on a one-to-one relationship.

• A query based on a one-to-many relationship. However, there are important differences depending on the version of SQL Server.

  SQL Server 2000 databases   You can update any table in the join because Access automatically detects the unique table in the relationship (The "many" side of a join).

  SQL Server 7.0 and 6.5 databases   You can update data based on a one-to-many join in a form and page, but only for fields from the unique table. When you open a query that contains a join in Datasheet view, it is read-only, by default. However, you can update data from a form or if you set the form's UniqueTable property (see discussion below).

• A query based on a self-join.
• A linked table based on a SQL Server Linked Server if the OLE DB data source allows.
• A UNION partitioned view (SQL Server 2000 only).

When you can't update data

In general, you cannot update data under the following circumstances:

• A query based on a many-to-many join.

• A query based on data marked as read-only in the database.

• A query that violates constraints on the base tables.

• A query that includes the DISTINCT keyword to exclude duplicate rows.

• A query based on a subquery that contains totals or aggregate functions.

• A form, datasheet, or page where the underlying SQL Statement of the record source contains a GROUP BY or COMPUTE clause.
• A linked table based on Transact SQL functions, because they create an ad hoc read-only connection to the external data source.

• In addition, you might not be able to update specific columns in the query results. The following list summarizes specific types of columns that you cannot update in the result set:

  • Columns based on expressions (such as price * quantity AS extended_price).

  • Rows or columns deleted by another user.

  • Rows or columns locked by another user (locked rows can usually be updated as soon as they are unlocked).

  • Timestamp or image columns.

How Access displays updated and default column values in a datasheet or form

Display of updated column values

When you are updating fields in a form, datasheet, or page, Microsoft Access can automatically insert a value for you, such as a default value, a calculated column, a timestamp value, an identity/AutoNumber field, or an action performed by a trigger. However, unlike a Microsoft Access database, which in most cases updates the field value as soon as you leave the field, in an Access project the display of the value or effect of the operation may not occur until after you commit the record depending on the version of the database you are using and several property settings. Access may update or perform the operation at the server first and then refresh the current display on the client.

After you update a record, such as by calculating columns, changing the value of a joined field, or updating a foreign key field, Access will fix up the corresponding join tables. In a SQL Server 2000 database, Access normally resynchronizes the data on the form or datasheet to reflect the changed values. However, because this row fix-up requires additional round trips to the server, you may want to disable this setting with the PerformResync property. In SQL Server version 7.0 and 6.5 databases, this row fix up must be set by the form designer using the ResyncCommand property (see discussion below).

Sorted and filtered results

For SQL Server 2000 databases, when you update a sorted and filtered query, you don't lose the sort and filter.

For SQL Server 7.0 and 6.5 databases, Access removes filters and sorts and it appears as if the record disappears from its current position and moves to a new location.

Display of default values

Access 2000 databases   Access displays default values when the form or datasheet displays new records by default. However, on a form, you can set the Fetch Defaults property to No to disable this for performance reasons.

Access 7.0 and 6.5 databases   Access does not display default values when the form or datasheet displays new records. However, any defaults you have on controls will be displayed.

Constraint validation and validation text

Access does not inform a user about column constraint violations or display the Validation Text property value until the user attempts to save the record.

Working with updatable snapshots in SQL Server 7.0 or 6.5 databases

Overview

Access supports only inserting, deleting, and updating data in a row of a unique table (the "many" side of a join) in an updatable snapshot that has been created by a one-to-many join operation in a view, row-returning stored procedure, or SQL SELECT statement. Access doesn't allow you to update fields on the "one" side of the join.

A Form or page that is based on a one-to-many join must define the UniqueTable property in order for the recordset to be updatable. The unique table (also called the "most-many" table) is the "many" side of a one-to-many relationship. It is called a unique table because one record from the unique table corresponds to, at most, one record in the view, row-returning stored procedure, or SQL statement. For example, in a view that joins the Customers and Orders tables, Orders is the unique table because one record from the Orders table corresponds to, at most, one unique record in the view. By contrast, one record in the Customers table can correspond to multiple records in the view.

Additionally, for a record source that is a stored procedure or SQL statement containing unnamed parameters, if you want to display the current values of a record after it is updated, you must also set the ResyncCommand property. Note that if you don't set the ResyncCommand property in these cases, the only consequence is that you won't see current values in a record after an update or insert. The update or insert will still happen correctly. For other types of record sources, Access displays current values even if the ResyncCommand property is not set.

In an updatable snapshot, you see only current values in an edited or newly inserted record after it has been successfully saved (see discussion above). For example, the Orders table contains an identity column as its primary key. When you insert data into this table, the new OrderID value appears after you save the record or move to another record (which implicitly saves the record). Similarly, in a form based on a view joining Customers and Orders, if you update the OrderID field to a different CustomerID, the Address field will not show the address of the new customer until after the record has been saved.

Finally, all key fields or the UniqueTable must be in the SQL SELECT statement select list (although not necessarily visible). You must also select all NOT NULL columns that do not have server defaults defined and supply values for these columns in the recordset. This does not apply to fields which have the Identity property set.

Recommended ways to update related data

The following are recommended ways to update related data:

• AutoForm is a handy way to create a form that allows updating of a view or stored procedure based on a join. Use AutoForm to quickly create the form, then switch to design view to set the UniqueTable property. You can also change the DefaultView property if you prefer a Datasheet view to a single Form view.
• Displaying several "filtered" forms allows you to base one form on a main table and have buttons on this form that pop up other modal forms that are filtered from the data in the main form. For example, the main form could be based on "Customers" and you would have a "View Orders For Customer" button that would open a second form based on the Orders table that filters by the CustomerID field on the main form.
• To update all data in a one-to-many relationship, you can create a form/subform combination. For example, instead of displaying data on a single form that joins orders and customers (in this case, the customer fields would be read-only), you can create a form for customers and a subform for orders. Because a form/subform combination uses two different recordsets, you can update all fields. Additionally, you can display this form/subform combination as a datasheet with a subdatasheet.

• In a page, you can simulate a form/subform combination by linking two pages using a hyperlink field, and then use Microsoft FrontPage to create two frames, one for each page.