1

I use MS Access 2010 with ADP to update sql database.

I wanted to partition some table using views and provide update permissions on each part to different user. Problem I have is that even when Access is set to work on a view it issues update on the source table causing "no permissions" error.

Is it possible to force Access to issue update on a view itself?

Kodak
  • 1,779
  • 2
  • 18
  • 29
  • ADP is deprecated, and not supported anymore since Office 2013... It use ADO and one if it's con is the issue you are experiencing: on VIEWS, ADO has the bad habbit to try to access the underlying tables instead of staying at view level, thus permission error in your case. There's nothing you can do apart coding passthrough UPDATE queries. But the best thing you can do is moving to accdb project with ODBC linked tables, you'll have to do that at some point anyway... – Thomas G Apr 18 '16 at 13:32
  • I did a quick test with a linked table and it looks like it solves my problem – Kodak Apr 18 '16 at 14:19
  • all I can say is that updating an ODBC linked view from SQL server, with integrated login, should work flawsly as long as the user has the right permissions on the view... Edit: seems that you edited your comment while posted mine. Glad it worked. – Thomas G Apr 18 '16 at 14:26
  • thx Thomas; would you like to post it as an answer? – Kodak Apr 18 '16 at 14:29
  • a litlle rep is always goog to grind, txs ;-) – Thomas G Apr 18 '16 at 14:31

1 Answers1

3

ADP is deprecated, and not supported anymore since Office 2013.

It uses ADO and one if it's con is the issue you are experiencing: on Views, ADO has the bad habbit to try to access the underlying Tables instead of staying at View level, thus permission error in your case.

There's nothing you can do apart coding passthrough UPDATE queries or use ODBC Linked views.

But the best thing you can do is moving to accdb project with full ODBC linked tables, you'll have to do that at some point anyway

Thomas G
  • 9,886
  • 7
  • 28
  • 41