Microsoft KB Archive/905476

= You cannot update the data in a view that you created in Office Access 2003 =

Article ID: 905476

Article Last Modified on 9/21/2005

-

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 &quot;Steps to reproduce the issue&quot; section. To follow these steps, first follow the steps in the &quot;Steps to reproduce the issue&quot; section. Then follow these steps to resolve this example issue.  In the database window, click New, click Create Text Scalar Function, and then click OK.  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  Click File, click Save, type LookupUser if you have to, and then click Save. On the File menu, click Close. In the database window, click the view that you saved in step 11 of the &quot;Steps to reproduce the issue&quot; section, and then click Design.  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) </li> On the File menu, click Save.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="moreinformation_section">

Steps to reproduce the issue
<ol> 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.</li> Click OK.</li> Click Display Database Window.</li> Add a table that is named Security to the database. Create the Security table with the following details: <ul> A column that has the column name ID and a data type of int</li> A column that has the column name UserID and a data type of varchar</li></ul> </li> In the left pane, click Queries.</li> In the right pane, double-click Create view in designer.</li> On the Add Table screen, click Close.</li> On the View menu, point to Show Panes, and then click SQL.</li>  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) </li> On the View menu, click Properties, click to select the Update using view rules check box, and then click OK.</li> On the File menu, click Save, type a name for the view, and then click OK.</li> On the File menu, click Close.</li> <li>In the database window, double-click the view that you saved in step 11.</li></ol>

<div class="references_section">