Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 307925 - Last Review: July 27, 2006 - Revision: 6.0
ACC2002: You Cannot Update a View in a Microsoft Access Project
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).
You cannot update data in a view in a Microsoft Access
project (ADP).
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:
- 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.
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
- Create a new SQL Server user. Set the user's default
database to Northwind.
- In the Northwind database on SQL Server, create the
following view and name it vwAllCustomers:
- 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.
- Log on as the new user. Open SQL Query Analyzer. Verify the
following:
- You cannot run the following as this user:
- You can run the following:
SELECT * FROM vwAllCustomers
- 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'
- 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.
For more information about security in Microsoft SQL
Server, see
SQL Server Books Online, which is available at the following Microsoft Web site:
APPLIES TO
- Microsoft Access 2002 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate