You cannot update the data in a view that you created in Office Access 2003 (905476)


The information in this article applies to:

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

SYMPTOMS

When you open a view that you created in Microsoft Office Access 2003, the data in the view is read-only. Therefore, you cannot update the data.

CAUSE

This issue occurs if the following conditions are true:
  • 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.

WORKAROUND

To work around this issue, follow these steps.

Note These steps work around the example issue that occurs when you follow the steps in the "Steps to reproduce the issue" section. To follow these steps, first follow the steps in the "Steps to reproduce the issue" section. Then follow these steps to resolve this example issue.
  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.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

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.

REFERENCES

For more information about how to resolve this issue in Microsoft Access 2002, click the following article number to view the article in the Microsoft Knowledge Base:

307925 You cannot update a view in a Microsoft Access project

Modification Type:MajorLast Reviewed:9/21/2005
Keywords:kbpending kbtshoot kbcode kbBug KB905476 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.