ACC2002: You Cannot Update a View in a Microsoft Access Project (307925)


The information in this article applies to:

  • Microsoft Access 2002
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:
  1. 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

  2. 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.

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

  1. Create a new SQL Server user. Set the user's default database to Northwind.
  2. In the Northwind database on SQL Server, create the following view and name it vwAllCustomers:
    SELECT * FROM Customers
  3. 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.
  4. Log on as the new user. Open SQL Query Analyzer. Verify the following:
    1. You cannot run the following as this user:
      SELECT * FROM Customers
    2. You can run the following:
      SELECT * FROM vwAllCustomers
    3. 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'
  5. 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.

REFERENCES

For more information about security in Microsoft SQL Server, see SQL Server Books Online, which is available at the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b

Modification Type:MajorLast Reviewed:7/27/2006
Keywords:kbprb KB307925
©2004 Microsoft Corporation. All rights reserved.