smk23 said:
I have a query that joins a table to a SQL view (odbc linked to .mbd
frontend) and would like to update records in the table. Because of the join
to the view, the query becomes non-updateable. How can I get around this?
sam,
--------------------------------------
Updatability Restrictions for Multiple-Table Queries
To be fully updatable, a query must meet several requirements:
You must specify an explicit inner or outer join between tables. Joins created implicitly
in the WHERE clause of the SELECT statement aren't updatable. For example, the following
join isn't updatable:
SELECT
Products.ProductID,
Products.ProductName,
Categories.CategoryID,
Categories.CategoryName
FROM Categories, Products
WHERE Products.CategoryID = Categories.CategoryID;
Summary (GROUP BY), UNION, DISTINCT, and crosstab queries are never updatable. Queries
joined to one or more summary queries aren't updatable, even if you don't attempt to
modify fields from an otherwise updatable table. However, a query may be updatable if it
refers to a summary query in a sub-SELECT statement, as in the following example:
SELECT Orders.*
FROM Orders
WHERE Orders.Freight >
(SELECT Avg(Orders.Freight) AS AvgOfFreight
FROM Orders
;
In this case, fields from the Orders table are updatable.
To be able to insert new records into a table in any query, all primary key fields must be
present.
While you're updating a single record in a query, changes to certain fields may render
certain other fields nonupdatable until the edit to the record is either saved or
canceled. As soon as the user edits data on the "one" side of a query, the join key on the
"many" side can no longer be modified. Usually, the "many" side's join key is updatable.
However, because data on the "one" side was modified first, this field is temporarily
rendered unmodifiable because row fix-up would discard changes to the "one" side's data.
As soon as the change to the "one" side of the query is saved or canceled, the "many"
side's join key becomes updatable again.
A change to a multiple-table query must not create orphaned records. You can change the
join key in the "many" table to a value already present in the "one" table, but you can't
specify a nonexistent value, except in the case of outer joins.
--------------------------------------
Sincerely,
Chris O.
