I need to be able to update multiple sections of an Access form using a SQL View that references multiple tables. If I was to update one table at a time and save it, it works fine. However, when I try to use more than one it stops working and throws the error, "ODBC -- update on linked table 'Q_student_record' failed. [Microsoft][ODBC SQL Server Driver][SQL Server] View or function 'dbo.Q_student_record' is not updatable because the modification affects multiple base tables. (#4405)"
I have heard that using an INSTEAD OF trigger would solve this problem, but I don't really know how to do that. If someone could explain how to do it or give an easier way that would help immensely.
My thought was using multiple sub forms and linking them into one bigger form thus only using one View per table. Would that work?
It is also possible to insert into a second table during an UPDATE, INSERT or DELETE operation by using the OUTPUT clause. Some limitations apply however. You can't do it if the two affected tables are on either side of a foreign key reference and you can only insert one row for each row affected by the UPDATE, INSERT or DELETE.
Thanks for the quick response. How do I go about making the stored procedure? I right clicked "New Stored Procedure..." which brings up a template but I don't know what to put where. Do I need to put the column names that are to be updated one by one or do I just need to put the view name that is updated and it will take care of the rest?