4

My database is an Access Data Project, tied to a SQL Server 2005 backend. I'm trying to bind a form to a view that uses an INSTEAD OF trigger. Access thinks the view isn't updatable, so it's making the form read-only; apparently it doesn't take the trigger into account.

I suspect the problem is that SQL Server's metadata says the view isn't updateable. Querying INFORMATION_SCHEMA.VIEWS, for example, shows IS_UPDATABLE = NO. Despite that, I definitely can update the view by using UPDATE statements or using the SSMS GUI.

Is anyone aware of a method I can use to convince Access that this view really is updatable? I know there are other ways I could get read-write access to this form, but I was planning to use this view to limit certain users' access to a very specific subset of data, and it would make things a lot easier if I could encapsulate all of that data within this one view.

Jeff Rosenberg
  • 3,522
  • 1
  • 18
  • 38
  • 2
    FYI, `INFORMATION_SCHEMA.VIEWS` will tell you every view is not updateable: http://msdn.microsoft.com/en-us/library/ms181381(v=sql.90).aspx – JNK Feb 26 '13 at 21:13
  • @JNK thanks, I thought it seemed weird that every single view was marked as not updatable. Is there a better way to check on whether a query is updatable? Any clue how Access determines it? – Jeff Rosenberg Feb 26 '13 at 21:16
  • 1
    I'm not an access pro, but it looks like you may need to create a unique index on your view for access to be able to update it. – JNK Feb 26 '13 at 21:17
  • Yeah, Access requires a PK to make any changes. – JNK Feb 26 '13 at 21:20
  • I'm not familiar with the Access Data Projects, but with the regular old linked table system, when Access couldn't determine a primary key, I seem to recall it used to offer the option to identify unique columns? – Cade Roux Feb 26 '13 at 21:45
  • @CadeRoux Access offers that option for .accdb databases, but not for .adp. And it doesn't seem that establishing a PK fixed the problem anyway. – Jeff Rosenberg Feb 26 '13 at 21:46
  • @JeffRosenberg I think unfortunately, you're kind of screwed. I don't see any good reason this shouldn't work except whatever Access is doing is too restrictive. You've done everything right to make your view updatable with a trigger, so it should be indistinguishable from a table for all clients. – Cade Roux Feb 26 '13 at 22:35
  • @Cade Roux I'm afraid you're right. Ugh. – Jeff Rosenberg Feb 27 '13 at 00:17

1 Answers1

1

Access requires a PK on the linked table in order for it to be updateable - I think this is so the JET (or whatever the new one is) engine can uniquely identify the row to change.

This means you need to convert this view into an indexed view, which is a whole other can of potentially very complicated worms.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • It's funny, I had actually set everything up to do this with indexed views, then decided against it because of the performance on updates. But if that's what it takes, I'll be happy to make it work. I'll give this a try right now. – Jeff Rosenberg Feb 26 '13 at 21:24
  • Then it may not be possible - I believe that for any view to be updateable it needs a PK. Access may just disallow updating views that reference more than one object - I'm not sure if it has the ability to check other system tables to ensure there is an instead of trigger in place for inserts/updates/deletes. – JNK Feb 26 '13 at 21:39