ACC2000: WITH CHECK OPTION Clause Is Not Enforced Within a View (213844)


The information in this article applies to:

  • Microsoft Access 2000
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:
  1. Open the sample Access project NorthwindCS.adp.
  2. Press CRTL+G to open the Immediate window.
  3. 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
  4. Use the following syntax to create a new stored procedure:
    Create Procedure spCheckOp
As
UPDATE vwCheckOp2
SET UnitPrice = 12
WHERE ProductID = 1
  5. 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.

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

  1. Open the sample Access project NorthwindCS.adp.
  2. Press CRTL+G to open the Immediate window.
  3. 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
  4. 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).
  5. Change the UnitPrice of the first record to a value of $15 or less.
  6. 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.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

198105 BUG: Errors When Creating a View Using the WITH CHECK OPTION in SQL Enterprise Manager

Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbprb KB213844
©2004 Microsoft Corporation. All rights reserved.