Microsoft KB Archive/213844

= ACC2000: WITH CHECK OPTION Clause Is Not Enforced Within a View =

Article ID: 213844

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q213844



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

Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
You can add new data to or you can modify existing data within a view even though the view implements WITH CHECK OPTION. Although you may receive the following Microsoft Access error when you save the new or modified record, the record is still saved, and the record is then removed from Datasheet view:

The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.



RESOLUTION
Use a stored procedure to add or update data within the view, where the view enforces WITH CHECK OPTION. For an example of how to do this, follow these steps:  Open the sample Access project NorthwindCS.adp. Press CRTL+G to open the Immediate window.  Type in the following statement to create the view: CurrentProject.Connection.Execute _ "CREATE VIEW vwCheckOp2 AS SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice > 15 WITH CHECK OPTION", _ False,False   Use the following syntax to create a new stored procedure: Create Procedure spCheckOp As UPDATE vwCheckOp2 SET UnitPrice = 12 WHERE ProductID = 1  Execute the stored procedure after you have closed and saved the procedure. Note that you receive the following message when you run the procedure, and that you cannot update the record:

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.



<div class="moreinformation_section">

MORE INFORMATION
The WITH CHECK OPTION is suppose to force all new and modified records within a view to continue to adhere to the criteria specified by the view's SQL statement. When a record is added or modified within a view, the WITH CHECK OPTION is also suppose to guarantee that the data entered or modified stays visible within the view after the new or edited record is committed.

However, when you apply WITH CHECK OPTION to a view, you find that you are allowed to enter or modify records that do not match the view's criteria. You are also allowed to successfully save the record, and the record does not remain in the view, but is instead removed from the view.

Steps to Reproduce the Behavior
 Open the sample Access project NorthwindCS.adp.</li> Press CRTL+G to open the Immediate window.</li>  Type the following statement to create a view: CurrentProject.Connection.Execute _ "CREATE VIEW vwCheckOp AS SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice > 15 WITH CHECK OPTION", _ False,False </li> In the Database window, click Views, and then execute vwCheckOp (if you do not see vwCheckOp under Views, press F5 to refresh the Database window).</li> Change the UnitPrice of the first record to a value of $15 or less.</li> Save the change and note the error as described in the "Symptoms" section of this article. Also note that the record is removed from Datasheet view.</li></ol>

<div class="references_section">