"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?