views:

42

answers:

1

"I'll use an Access ADP" I said, "it's only a tiny project and I've got better things to do", I said, "I can build an interface really quickly in Access" I said.

</sarcasm>

Sorry for the rant, but it's Friday, I have a date in just under two hours, and I'm here late because this just isn't working - so, in despair, I turn to SO for help.

  • Access ADP front-end, linked to a SQL Server 2008 database
  • Using a SQL Server account to log into the database (for testing); this account is a member of the role, "Api"; this role has SELECT, EXECUTE, INSERT, UPDATE, DELETE access to the "Api" schema
  • The "Api" schema is owned by "dbo"
  • All tables have a corresponding view in the Api schema: e.g. dbo.Customer --> Api.Customers
  • The rationale is that users don't have direct table access, but can deal with views as if they were tables

I can log into SQL using my test login, and it works fine: no access to the tables, but I can select, insert, update and delete from the Api views.

In Access, I see the views, I can open them, but whenever I try to insert or update, I get the following error:

The SELECT permission was denied on the object '[Table name which the view is using]', database '[database name]', schema 'dbo'

Crazy as it sounds, Access seems to be trying to access the underlying table rather than the view.

Any ideas?

A: 

Could it be because of this: "To update a partitioned view, the user must have INSERT, UPDATE, and DELETE permissions on the member tables."

http://msdn.microsoft.com/en-us/library/ms187956.aspx

AaronLS