Access Cookbook - Ken Getz [298]
Figure 14-19. Using SQL Server Enterprise Manager to grant permissions for views
You can then update data through the view instead of the table, as long as you have permissions granted on the view. This allows you to control which rows and columns in the table your users can access.
However, in an ADP, Access doesn't use the view to update the data even if the view is the record source of a form. A peek at a Profiler session in SQL Server shows that when you update the form, Access creates an update statement directly against the base tables.
You can solve this problem by adding an option to the view that will force Access to run its updates against the view rather than against the base tables. If you use the WITH VIEW_METADATA option when you create (or alter) your view, SQL Server will send Access metadata (column names and data types) from the view rather than from the underlying tables, and Access will use the view to update data. If you use Access to create the view, you can set this option by setting the "Update using view rules" property, as shown in Figure 14-20.
Figure 14-20. The "Update using view rules" property
TIP
If you are using Access 2000, you need to type in the WITH VIEW_METADATA option manually, since it doesn't show up in the Properties dialog. This option wasn't supported in SQL Server 7.0.
Use the following steps to allow your users to update data through views when they don't have permissions on the underlying tables:
Revoke or deny all permissions to the public role for the table (or tables) on which the view will be based.
Create a view that selects data from the table by using the VIEW_METADATA option or selecting the "Update using view rules" checkbox. This example selects data from the Shippers table:
CREATE VIEW vwShipperList
WITH VIEW_METADATA
AS
SELECT ShipperID, CompanyName, Phone
FROM Shippers
Grant INSERT and UPDATE permissions on the view.
To test the view, use it as the record source of a form. Make sure to fully qualify your references with the ownername.objectname syntax.
Log on as another user who does not have permission on the underlying tables. You should now be able to update data or insert data, but not delete an existing shipper.
Discussion
When Access requests data for a view in browse mode, it also retrieves metadata that it uses to construct update, insert, and delete statements. The VIEW_METADATA option specifies that SQL Server returns enough metadata information about the view for Access to implement updateable client-side cursors that work with the view instead of the base tables.
This technique is not available in SQL Server 7.0 or earlier because the VIEW_METADATA option did not exist prior to the release of SQL Server 2000. This new feature makes it possible to take advantage of bound Access forms without having to sacrifice security. Few SQL Server database administrators are willing to give users unrestricted permissions to update tables. Views offer more control, but the most control comes from using stored procedures, and, unfortunately, there is nothing like the VIEW_METADATA option for stored procedures.
Chapter 15. Office Web and SharePoint
Data Access Pages represent just one of several options for creating web pages connected to Access databases. You can also use FrontPage 2003, with or without Windows SharePoint Services, to create Access-connected web pages.
Windows SharePoint Services, a server component that is part of Windows 2003 Server, is a great system for easily creating collaborative, team-based web sites consisting of various lists and documents. Access 2003 can work with SharePoint in two ways. First, you can use Access as a frontend to your SharePoint list and documents. That is, you can create a SharePoint