Single-Table Query Updatability

A query based on a single table can select some or all records, and some or all fields, from that table. In the simplest case, the query returns all records and all fields. For example:

SELECT * 
FROM Customers;

Every record retrieved, and every field in every record, is accessible and subject to change (except calculated fields based on expressions). Every record can be deleted and new records can be inserted.

If you were to use Microsoft Access to open a datasheet based on this query, you would be able to freely browse forward and backward, making any changes you wanted (subject to security restrictions, validation rules, and referential integrity constraints). Any changes made to the Recordset object created by this QueryDef object would automatically be reflected in the underlying table.

A query can also select and sort records from the original table and remain updatable:

SELECT *
FROM Customers
WHERE Customers.ContactTitle = 'Owner'
ORDER BY Customers.CompanyName;

An updatable single-table query such as this one can be useful in a data-entry situation in which you would like to present the records to the user in a certain order, or hide certain records based on the selection criteria.

One thing to be aware of, however, is that Microsoft Jet doesn’t prevent the user from adding a new record through a QueryDef object that wouldn’t have met the original selection criteria. For example, the previous query selects only records in which the value of the ContactTitle field is “Owner.” The user can add a new record and specify a value other than “Owner” for the ContactTitle field, in which case the user would be adding a record that the query, if subsequently rerun, would no longer select. It’s up to your application to enforce insertion restrictions such as this.