About updating data in a Microsoft Access project or data access page

The way Microsoft Access updates data in a Microsoft Access project or data access page is different from the way they update data in a Microsoft Access database. Both users and programmers need to be aware of these differences. This topic provides reference information about:

General rules for updating data

Working with updatable snapshots

When updated field values are displayed

Recommended ways to update related data

General rules for updating data

The following rules apply when you are updating data from an Access project or page connected to either an Access database or Microsoft SQL Server database:

• 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.
  
  
• You can update data based on a one-to-many join in a form and page, but only for fields from the "many" side of the join. When you open a view or run a stored procedure that contains a join in Datasheet view, it is read-only, by default. However, you can update data from a form or page, even if it has a view or stored procedure as a record source, if you set the form or page's UniqueTable property (see discussion below).
  
  
• To update a view or stored procedure, you must have UPDATE, INSERT and DELETE permission on the base table(s) referenced in the view or procedure, as well as SELECT permission on the view or EXECUTE permission on the procedure.
  
  
• You cannot update data in a grouped page, only a non-grouped page.
  
  
• You cannot update data derived from a self-join (such as all employees and the managers they report to) under any circumstances.
  
  
• As is the case with an Access database:
  • You cannot update a form, datasheet, or page where the underlying SQL Statement of the record source contains a GROUP BY or COMPUTE clause.
    
    
  • You cannot update fields based on expressions (such as "price * quantity AS extended_price")
    
    
  • You cannot update a form, datasheet, or page based on a many-to-many join.

Return to top

Working with updatable snapshots

Access 2000 supports only inserting, deleting, and updating data in the 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 statement. Access 2000 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 parameter markers, 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 below). For example, the Orders table contains an identity column as its primary key. When you insert 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 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 except for fields with the Identity property set.

Return to top

When updated field values are displayed

In general, when you are updating fields in a form, datasheet, or page, Access can automatically insert a value for you, such as a default value, a calculated field, a timestamp value, an identity/AutoNumber field, or action performed by a trigger. However, unlike an Access database, the display of the value or effect of the operation occurs after you commit the record. This occurs because Access must update or perform the operation at the server first and then refresh the current display on the client.

Return to top

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 set the DefaultView property if you prefer a Datasheet view to a single Form view.
  
  
• 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 with customers and a subform with orders. Because a form/subform 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.

Return to top