Terry said:
I have an Access database application that I have worked on for
several years. I have upsized all the tables from Access to SQL
Server. But now I find that when I try to run some of my queries
they don't work.
Example: I have a Query in Access that links three tables together.
All three tables have a Key field. When i run the query in Access, I
can update the information in the query. But after I upsized the
tables to SQL and make a link to these tables in the SQL Server, when
the query runs it displays the correct information, but I can not
make any changes to the query results.
Very, Very New to SQL Server and I am lost. Can anyone help.
SQL Server is much stricter than Jet about updatable views. Here is
what SQL Books OnLine (BOL - if you don't have this on your desktop,
download it from the relevant link from this page:
http://databases.aspfaq.com/database/where-can-i-get-this-books-online-documentation.html)
has to say on the subject:
******************************************************************
Updatable Views
You can modify the data of an underlying base table through a view, as
long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must
reference columns from _only one base table_.
The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in
any other way, such as through the following:
An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a
computation and are also not updatable.
The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
The previous restrictions apply to any subqueries in the FROM clause of
the view, just as they apply to the view itself. Generally, the Database
Engine must be able to unambiguously trace modifications from the view
definition to one base table. For more information, see Modifying Data
Through a View.
If the previous restrictions prevent you from modifying data directly
through a view, consider the following options:
INSTEAD OF Triggers
INSTEAD OF triggers can be created on a view to make a view updatable.
The INSTEAD OF trigger is executed instead of the data modification
statement on which the trigger is defined. This trigger lets the user
specify the set of actions that must happen to process the data
modification statement. Therefore, if an INSTEAD OF trigger exists for a
view on a specific data modification statement (INSERT, UPDATE, or
DELETE), the corresponding view is updatable through that statement. For
more information about INSTEAD OF triggers, see Designing INSTEAD OF
Triggers.
******************************************************************
I have seen some prior posts in these forums about people having
problems with views with INSTEAD OF triggers. Personally, I would give
up on the idea of simultaneously updating all 3 tables and write some
code to do them individually.
