In this article we look at problems which can occur if you try and update a view, making changes which will cause updates on multiple base tables in the view, and we discuss how to resolve this problem in SQL Server.

For our example consider creating a view which combines fields from both the Orders and [Order Detail] tables in the Northwind training database.

Now we create the following view.

CREATE VIEW TwoTableView AS SELECT Orders.OrderID, ProductId, Quantity, OrderDate FROM Orders    INNER JOIN [Order Details]       ON Orders.OrderID = [Order Details].OrderID

If we link Access to this view, and tell Access which fields form the unique record identifier in the view, then the linked view will be updateable.

Shown below is the resulting linked view.

If we try editing the Quantity field in the view (which comes from the [Orders Details] base table) and save the record, then the changes are saved.

If we try editing the OrderDate field in the view (which comes from the [Orders] base table) and save the record, then the changes are saved.

But if we try and change both the Quantity and OrderDate and save the record this will not work.

We get the error message that the view is not updateable because the modification affects multiple base tables.

This is a limiting feature in SQL Server, where you cannot try and generate an update on a view when the update would affect multiple base tables. You will also find that you cannot either INSERT or DELETE records, because again this would affect multiple base tables.

Working around the problem

The simplest work around to this problem is to ensure that when editing through a view like this, changes are saved after each individual field is edited.

In Access we could use the following form :-

Then by adding code behind the AfterUpdate events on each appropriate control we force the record to be saved.

Private Sub OrderDate_AfterUpdate()    RunCommand acCmdSaveRecord End Sub Private Sub Quantity_AfterUpdate()    RunCommand acCmdSaveRecord End Sub

SQL Server Management Studio

Editing the OrderDate with the SQL Server using the management studio allows the record to be saved.

But if you try and edit only the quantity with the management studio you get the following error:-

The data in row1 was not commited....

But executing the SQL to do this will work!

UPDATE TwoTableView SET Quantity = 13 WHERE OrderId = 10248 And ProductId = 11

Our advice here is that this is a strange behaviour of the management studio which you can ignore, as if you run the profiler and pick up the SQL generated, and paste and execute it SQL Server, then that also works! So don't worry about this.

Below we show the SQL captured from a profiler trace, which if you execute in SQL Server, will work! So this is just a bug in the Management Studio.

exec sp_executesql N'UPDATE TOP (200) TwoTableView SET Quantity = @Quantity WHERE (OrderID = @Param1) AND (ProductId = @Param2) AND (Quantity = @Param3) AND (OrderDate = @Param4)',N'@Quantity smallint,@Param1 int,@Param2 int, @Param3 smallint,@Param4 datetime',@Quantity=14,@Param1=10248,@Param2=11, @Param3=14,@Param4='1996-07-18 00:00:00'

Attempting to execute the following SQL, fails as expected. Again the soloution would be to execute 2 separate updates.

UPDATE TwoTableView SET Quantity = 13, OrderDate = '1 january 2010' WHERE OrderId = 10248 And Productid = 11

Msg 4405, Level 16, State 1, Line 1 View or function 'TwoTableView' is not updatable because the modification affects multiple base tables.

Summary

We hope that this article has helped to explain some of the mysteries behind using a view based on multiple base tables to update data and shows you the limitations and work arounds to this problem.

Thanks

I would like to thanks Jim McLeod of Abacus M.C.T.A. Limited for bringing this issue to our attention.

Top of Page Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz