Microsoft KB Archive/307925

= ACC2002: You Cannot Update a View in a Microsoft Access Project =

Article ID: 307925

Article Last Modified on 7/27/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q307925



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
You cannot update data in a view in a Microsoft Access project (ADP).



RESOLUTION
To resolve this issue, do the following:  Install the Microsoft Data Access Components (MDAC) 2.6 on the client computer. You can download MDAC 2.6 from the following Microsoft Web site:

http://msdn.microsoft.com/data/Default.aspx

 Verify the following setting:  In Microsoft Access, open your view in Design view. On the View menu, click Properties. On the View tab, make sure that the Update using view rules check box is selected.

NOTE: This tells the Windows Cursor Engine in MDAC not to try the update by using the base tables, but to update directly against the view definition. </li></ol>

<div class="moreinformation_section">

MORE INFORMATION
The Update using view rules property indicates that all updates and insertions to the view will be translated by MDAC into SQL statements that refer to the view, rather than into SQL statements that refer directly to the base tables of the view.

Steps to Reproduce the Behavior
<ol> Create a new SQL Server user. Set the user's default database to Northwind.</li>  In the Northwind database on SQL Server, create the following view and name it vwAllCustomers : SELECT * FROM Customers </li> Give the new user full permissions to vwAllCustomers. Give the new user no permissions to the underlying Customers table. Remove permissions of the public role to the Customers table.</li> Log on as the new user. Open SQL Query Analyzer. Verify the following: <ol style="list-style-type: lower-alpha;">  You cannot run the following as this user: SELECT * FROM Customers </li>  You can run the following: SELECT * FROM vwAllCustomers </li>  You can run the following; that is, the view permissions allow the user to update the data through the view, but allow no access to the underlying table. UPDATE vwAllCustomers SET ContactName = 'Maria Anderson' WHERE CustomerID = 'ALFKI' </li></ol> </li> Create a new ADP as the new user that uses the existing Northwind database as the back end.

Note that if you try to edit data through the vwAllCustomer view, you cannot do so.</li></ol>

<div class="references_section">