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