Access Projects

[lherrick]

I have developed an Access Project with SQL Server as a backend, using NT Windows Security. I have created views to limit the columns and rows that my users can access and update. According to Microsoft, no permissions on the underlying tables are necessary in order for users to be able to select, update, etc. the records in the views. This works fine if I'm updating via a T-SQL statement. However, if I open the view in Project, the user cannot do anything except select the record. I have looked on the Microsoft site and their information on Access Project states that the user needs update, insert and delete permission on the underlying table. I tried that, with the same result. The only way my users can update and/or insert the view through Project is if I give them select permission on the underlying table, which completely defeats the purpose. Can someone tell me if I'm missing something here?

 

[cmmrfrds]

Check this link.

http://support.microsoft.com/default.aspx/kb/253673

In order to update a base table, ADO (driver in the access project) needs to identify the record to be updated.

Is the primary key of the table you want to update included in the view?

Another unique identification of a row is a timestamp column in the table.

If there is a timestamp column on a table, sql server will pass it back to ADO behind the scenes so you don't need to include it in the view.

What I would try first is to add a timestamp column to the tables you want to update through the view.

 

[lherrick]

Thanks so much for responding. My table does include a primary key, and, in fact, all fields in the table are included in the view. Are you saying that even with a primary key, a timestamp column may be necessary? I will try that.

Thanks again.

 

[cmmrfrds]

By primary key that means it is defined as an unique index on the table. Right?
If there is a timestamp column ADO can rely on that to identify the record to be updated. The timestamp should work regradless of the primay key.

 

[cmmrfrds]

After thinking about it some more, I don't think you can get by without Select persmissions on the table using a View to update. You might need to use a stored procedure instead of a view where you can control what is returned to the user.