ACC: Field and Record Validation Rule Enforced at Engine Level

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

All versions of Microsoft Access (except Microsoft Access 1.x) enforce field and record validation rules defined at the table level, regardless of the data input or editing method.

MORE INFORMATION

In Microsoft Access version 1.x, field validation defined at the table level is not enforced by the database engine. The validation rule and validation text defined in table design are used as the defaults for the ValidationRule and ValidationText properties in forms. There is no record validation in Microsoft Access 1.x.

In all other versions of Microsoft Access, the validation rule for both the field and table record are enforced at the engine level. The validation rules are enforced whenever you add or edit data, whether through a table's datasheet, a form based on the table, an append query, an update query, Visual Basic or Access Basic code, or by importing data from another table.

If you have converted a database from Microsoft Access 1.x to a later version of Microsoft Access, you can test to see if the data in the table meets the validation rules with the Test Validation Rules command, available by clicking the right mouse button on the title bar of the Table Design window.

The expression for a field validation rule cannot refer to fields or controls in a field validation rule, user-defined functions, Microsoft Access domain functions, aggregate functions, or the CurrentUser() or Eval() functions. The expression for a record validation rule cannot contain user-defined functions, Microsoft Access domain functions, aggregate functions, or references to forms, queries, or tables. Expressions can include only references to fields in the table. If you try to do field level validation on a table including any of the above options you will encounter the following error message:

   In Microsoft Access 97:

   The database engine does not recognize either the field '<Field Name>'
   in a validation expression, or the default value in the table
   '<Table Name>'.

   In Microsoft Access 2.0 and 7.0:

   Unknown or invalid reference '<Field Name>' in validation expression or
   default value in table '<Table Name>'.

   Unknown function '<Function Name>' in validation expression or default
   value on '<[Table Name]>.[<Field Name>]'

   Field Validation
   Rule Defined       Form Validation
   (Table Level)      Rule Defined      Behavior
   ----------------------------------------------------------------------
   No                 No                No validation is performed
                                        at any level.

   Yes                No                Validation will be enforced
                                        regardless of method used
                                        to insert data.

   No                 Yes               Validation enforced at form level
                                        only. User can bypass the form
                                        validation rule when editing data
                                        outside the form.
                                        This is the same as Microsoft
                                        Access 1.x behavior.

   Yes                Yes               When editing data in a form, form
                                        validation rules will be enforced
                                        first. If the value passes form
                                        validation, then the field
                                        validation rule is enforced. In
                                        order for data to be accepted, it
                                        must pass both rules.

REFERENCES