Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    47

    Unanswered: Update Multiple Tables In One View

    Hi,

    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?

    Thanks,
    Andrew

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Use a stored procedure to update both tables.

    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.

  3. #3
    Join Date
    Jul 2009
    Posts
    47
    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?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •