Geek Answers Handbook

Unable to update view using INSTEAD OF trigger

My database is an access data project bound to SQL Server 2005. I am trying to bind a form to a view that uses the INSTEAD OF trigger. Access believes that the view is not updatable, so it makes the form read-only; apparently it does not account for the trigger.

I suspect the problem is that SQL Server metadata says the view is not updating. For example, the query INFORMATION_SCHEMA.VIEWS shows IS_UPDATABLE = NO. Despite this, I can definitely update the view using the UPDATE or using the SSMS GUI.

Does anyone know of a method that I can use to convince Access that this view is indeed being updated? I know there are other ways to read and write access to this form, but I planned to use this view to restrict certain users access to a very specific subset of the data, and that would make it a lot easier if I could encapsulate all of this data into this one view.

+4
source share
1 answer

Access requires a PK in the linked table so that it can be updated. I think this means that JET (or something new) can uniquely identify the string to be modified.

This means that you need to convert this view to an indexed view, which is a whole other potentially very complex worms.

+1
source

More articles:


All Articles