View cannot be updated because modification affects multiple base tables

[Shaimaa T]

Hi all,

I am trying to trap the database error "View cannot be updated because modification affects multiple base tables" in the Form OnError event to display a user-friendly message instead of the above.

The above can happen in the scenario of inserting/updating several fields of different tables at one time, likewise what I am facing now is the scenario of the user copying an entire record and pasting it.

I tried
Case 4405
MsgBox "Can not update the record. it is related to base tables"
Response = acDataErrContinue

But it doesn't work. How can I know the case number and solve all the scenarios of multiple insert/update attempts?

Also, are there any better solution than this? I first tried to detach the SQL statements out of the views and make it one select statement so that I can normally multiple update/insert(as this problem happens with views only) but since CTE is not supported in Access , I failed to do so.

Any help with the above and/or ideas?

[jonh]

Queries are only updateable if the tables are linked by primary keys.

[Shaimaa T]

So how can I trap the attempt to paste an entire record?

Since using views the links are not primary keys as a view does not have a primary key

[jonh]

Tables have primary keys. Views (Queries in Access) are based on tables. Link 2 tables together by pk in your view and it should be updateable.

If the view is not updatebale then there's no point giving your users the option of updating it. Disable the options to get rid of the errors.

[Shaimaa T]

So I have 2 views
One is
SELECT some stuff
FROM dbo.Culture RIGHT OUTER JOIN
dbo.Trial ON dbo.Culture.cultureID = dbo.Trial.CultureID LEFT OUTER JOIN
dbo.TrialCultureSteps_view_part1 ON dbo.Culture.cultureID = dbo.TrialCultureSteps_view_part1.cultureID

Here Culture ID is a primary key in Culture and a Trial's foreign Key

And in the 2nd view :

SELECT DISTINCT dbo.Culture.cultureID,
(SELECT TOP (1) WeekNr
FROM dbo.CultureStep
WHERE (CultureID = dbo.Culture.cultureID)
ORDER BY CultureStepID) AS normalstartweek
FROM dbo.Culture INNER JOIN
dbo.CultureStep AS CultureStep_1 ON dbo.Culture.cultureID = CultureStep_1.CultureID

CultureID is a primary key for culture and Culture Step's foreign key.

So I am already doing as you mentioned right? And still I cannot update fields from different table at the same time.

[jonh]

I'm not saying that your query/view *should* be updateable.
I'm not an expert in SQL Server sql and I can't be bothered to set up a test to try it out.

But in Access, Queries (maybe Views too) are updateable if tables are linked (directly) by Primary Keys.

e.g. in this case id is a primary key on three tables, and all fields are updateable :

SELECT Table1.*, Table2.*, Table3.*
FROM (Table1 LEFT JOIN Table2 ON Table1.id = Table2.id) LEFT JOIN Table3 ON Table1.id = Table3.id;

[Shaimaa T]

But id in our case(Culture ID) is not a primary key on the 3 tables.

[jonh]

So make the form read only.