0

I have an MS Access db with a linked table pointing to an MS SQL server database view. We have been using MS Access as a simple front end to change data contents via this view. The view was referencing only one table so it worked smoothly, we had no problem.

Now, I got a request to put in a field into that view that is from another table. The new field should serve only informative purpose, there is no need to update it, only the contents of the original table.

My problem is that if I put this field into my view (joining the other table in the view) I am unable to update the row in MS access because I am getting an error message:

"The record has been changed by another user since you started editing it. If you save the record you will overwrite the changes the other user made."

I am having a similar error message if I try to delete. Only inserting a record works.

I understand that this happens because SQL server and Access both try to modify the record. My question: is there way to make this work somehow?

I would really appreciate the help! Thanks

Tamas

Improve this question

1 Answer 1

Reset to default
1

You should be able to achieve what you want by creating an Instead Of trigger on the view. This sort of trigger replaces the update or delete against the view with the custom trigger code. See

https://www.mssqltips.com/sqlservertip/5984/sql-server-trigger-on-view-example/

Improve this answer
1
  • Thanks for replying. Yes, since then I have added instead of triggers (insert update and delete) for that view. But unfortunately, it does not help.
    – Tamás Vándorffy
    Jun 25, 2021 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.