UniqueTable Property

See Also         Example        

You can use the UniqueTable property to specify or determine the table to be updatable when:

• A form or data access page is bound to a multiple table view or stored procedure within a Microsoft Access project (.adp).
  
  
• A data access page is bound to a multiple table query or macro within a Microsoft Access project (.adp).

Setting

The UniqueTable property is a string expression representing the unique table to be updatable.

You can set this property by using the property sheet or Visual Basic.

Remarks

The UniqueTable property identifies the "most many" table of a join. If you don't set the UniqueTable property, a form that is bound to a view or stored proc or SQL String containing a join is read only. Also, the datasheet produced by View.Open or StoredProc.Run is read only in the case of a join (because there's no way to set the UniqueTable property). Once you set the UniqueTable property, only fields from that table are updatable, and inserts and deletes can only be made to that table.

A form or data access page based on a join cursor must have a UniqueTable property string in order for the recordset to be an updatable snapshot. The Unique Table is the table in the underlying query whose rows have a 1-to-1 correspondence with rows in the cursor. In a simple Patients - Doctors join, Patients is the unique table because every row of the cursor corresponds to one row of the Patients table. Note that a Many-to-Many join does not have a valid UniqueTable property, and is thus read-only. The UniqueTable property will be exposed as a RecordsetDef object in the case of a data access page. The purpose of the UniqueTable property is:

To enforce the correct updatability semantics:

• The key columns of the Unique Table must be present in the select list of the query that forms the cursor, even for SQL Server. (For other data sources, see the Remarks section in the ResyncCommand property topic.)
  
  
• Deletion of a row in a join cursor deletes the row from the Unique Table only.
  
  
• Insertion of a row in a join cursor is allowed for the Unique Table only
  
  
• Update of a row in a join cursor is allowed for fields in the Unique Table only.

To provide the right parameter values for the Resync Query. The UniqueTable property of a form or a RecordsetDef supports the catalog.owner.tablename notation to fully qualify a base table from others in the same cursor, if this is required. For example, for example, if dbo.authors were joined to user1.authors in a cursor, then the UniqueTable would need to be specified as dbo.authors or user1.authors.

For a join cursor, if the UniqueTable property is empty, the recordset reverts to read only and any attempt to edit results in a beep and a status message, "This Recordset is not updatable because the UniqueTable property is not set." If there is a non-empty UniqueTable property, set the UniqueTable property (and UniqueSchema, UniqueCatalog properties if necessary) on the underlying Recordset or Rowset. Then, go through and mark each column that does not match the UniqueTable property as read only.

On insert and update operations, only the fields from the Unique Table are available for editing. When the user tries to type into them he gets a beep and the message "Only fields from the Unique Table can be edited." If the UniqueTable property has been set incorrectly, this will happen for all columns.

Set Unique Table in the single table case

If the case of a recordset based on a view or SQL String, we still want to provide our own ResyncCommand to handle the case of server expression columns. Also, in the case of a stored proc, the user may want to provide their own ResyncCommand property even in the single-table case. For these reasons we should always set the "Unique Table" provider property in the single table case. ( QBU will only allow the setting of a Resync Command if Unique Table has been set. )