• The Update using view rules option is selected in the properties for the view.
• The view contains a JOIN statement or a subquery.
  
  Note The JOIN statement can be an INNER JOIN or OUTER JOIN statement.

Back to the top

1. In the database window, click New, click Create Text Scalar Function, and then click OK.
2. In the Function pane, paste the following function:

   CREATE FUNCTION dbo.LookupUser
   (@ID int)
   RETURNS varchar(50)
   AS
   BEGIN
   DECLARE @usr varchar(50)
   SELECT @usr=UserID FROM dbo.Security Where ID=@ID and UserID=CURRENT_USER
   
   RETURN @usr
   END

3. Click File, click Save, type LookupUser if you have to, and then click Save.
4. On the File menu, click Close.
5. In the database window, click the view that you saved in step 11 of the "Steps to reproduce the issue" section, and then click Design.
6. In the SQL pane, replace the existing SQL statement with the following statement:

   SELECT OrderID, CustomerID, EmployeeID, OrderDate
   FROM dbo.Orders
   WHERE (dbo.LookupUser(EmployeeID) = CURRENT_USER)

7. On the File menu, click Save.

Back to the top

Steps to reproduce the issue

1. Start Office Access 2003, and then open the NorthwindCS.adp project.
   
   Note By default, the NorthwindCS.adp project is located in the \Microsoft Office\Office 11\Samples folder.
2. Click OK.
3. Click Display Database Window.
4. Add a table that is named Security to the database. Create the Security table with the following details:
   • A column that has the column name ID and a data type of int
   • A column that has the column name UserID and a data type of varchar
5. In the left pane, click Queries.
6. In the right pane, double-click Create view in designer.
7. On the Add Table screen, click Close.
8. On the View menu, point to Show Panes, and then click SQL.
9. In the SQL pane, paste the following SQL statement:

   SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.EmployeeID, 
   dbo.Orders.OrderDate
   FROM dbo.Orders INNER JOIN
   dbo.Security ON dbo.Orders.EmployeeID = dbo.Security.ID
   WHERE (dbo.Security.UserID = CURRENT_USER)

10. On the View menu, click Properties, click to select the Update using view rules check box, and then click OK.
11. On the File menu, click Save, type a name for the view, and then click OK.
12. On the File menu, click Close.
13. In the database window, double-click the view that you saved in step 11.

Back to the top