Table Level Validation Rules in Microsoft Access

by Philipp Stiefel, originally published December 27th, 2017
last updated 2021-01-08

Based on a photo by Glenn Carstens-Peters, used here under CC0 licensing

They are often overlooked, nevertheless, they are a vital part of database design. Data Validation Rules in Access tables. – Also know as CHECK CONSTRAINTs by SQL Standard terms and in most other database management systems.

In Access forms, you can implement a vast variety of custom validation rules for the data being entered by users in that particular form. When using Before Update (or Insert, Delete) Event Procedures of controls or the form itself, the possibilities are virtually limitless.

Compared to data validation in an Access form, validation rules in tables have got a very limited set of possible operations. Only a limited subset of built-in (VBA) functions is available. These do not include the Domain Aggregate Functions, like DSum or DCount, which I miss most dearly. And, of course, you cannot use your own custom VBA functions in any validation rule. - This essentially limits any validation rule to work with the current record it is validating.

On the other hand, validation rules directly implemented in the table design have one very significant advantage. They are always enforced. Regardless if data is entered in any particular form, inserted or changed via an Update or Append Query, or imported from an external file. Whenever data is in a table is modified, the validation rules will be checked.

There are two very similar types of validation rules you can use in the table design. (Actually, there are three types, but more on that later)

The most commonly used validation rules are probably the field validation rules. Within these validation rules, you can only reference the one particular field the validation rule applies to.

To create a validation rule, you can either type it manually into the corresponding text box in the field properties, or you click the […]-button to invoke the Expression Builder. I personally prefer to type expressions manually, but the Expression Builder has the advantage that it only lists those functions you can actually use in a validation rule expression.

One thing I find quite useful is you can also use the SQL comparison operators LIKE, IN and BETWEEN in your validation expressions.

Here is an example expression for a validation rule for the field TextCol.

(The List Separator on my system is the semicolon, if you have configured the comma as List Separator, you should use commas instead of the semicolons in the above expression.)

A very helpful addition to the validation rule itself is the Validation Text property. You can enter an informative text there that is displayed to the user if a record cannot be saved because of a violation of the rule.

Table level validation rules are very similar to field validation rules. The main difference is that you can reference any column from the table in your validation rule expression.

Here is an example of a table-level validation rule. Both TextCol and NumberCol are columns of that table.

One minor limitation of these table validation rules is, there can be only one rule per table. So, if you need to check several different aspects of any one record for validity, you need to use the AND and OR operators to combine all those checks into one big rule.

And finally one important detail you should know about validation rules. If a Validation Rule Expression evaluates to NULL, it is considered valid! That is somewhat counterintuitive and you need to keep that in mind.

There are many examples of validation rules in the article Restrict data input by using validation rules on the Microsoft Office Website.

Version 4 of the Jet database engine, included with Access 2000 (yes, a long time ago), introduced one significant enhancement that is frequently overlooked. - Check Constraints.

A Check Constraint is very similar to a table level validation rule with one very major difference. You can use SQL Statements in Check Constraints. So, they are not limited to just referencing the current record in the expression, but you can reference any record in any table of your database. This includes values aggregated with SUM, COUNT, or any other aggregate function.

Unfortunately, you cannot define any validation message for Check Constraints. Your users will be presented a rather cryptic message that the entered data violates a validation rule without further details.

The reason those are frequently overlooked is, there is no user interface to configure them. The DAO Object Library, used by default in Access, does not recognize the syntax to create them. - So, if you can neither create them in them in the UI nor create them with a (DAO) DDL (Data Definition Language) query, how can you use them at all?

You need to execute a DDL query via an ADODB.Connection to your Access database. - This sounds more complicated than it actually is. With Access 2000 there was the new Currentproject.Connection property introduced, that returns an open ADODB.Connection to the current database, similar to the CurrentDb-Method returning an open DAO.Database instance.

The SQL syntax for Check Constraints is:

The check expression can be any expression you can use as validation rule (You do not use the List Separator inside the check expression, but always the comma). Additionally, you can also use any SQL query returning a single value for evaluation in the expression.

Here is an example of a Check Constraint using the record count from a second table to check data input for validity.

To actually execute this DDL Statement and create the constraint, you would use the Execute-Method of the CurrentProject.Connection, like this:

If you reference records from another table in the constraint, that reference is only evaluated when entering or modifying records in the main table, the one the constraint applies to. It is not evaluated when the data in the referenced table changes.

In the above example, inserting records in the tblOtherTable could make records in table tblValidationRuleTest invalid by the measure of the Check Constraint. However, this is not checked, as long as the records in tblValidationRuleTest are not modified.

You should be aware of the performance implications of using complex SQL queries in any Check Constraint. This SQL will be executed for every record inserted or updated. So, you should only use queries in there, which are running pretty fast.

As there is no UI to manage them, these Check Constraints are pretty hard to detect if you are not aware of their existence. You can only see them in the, usually hidden, MSysObjects system table. You can use a query like this to list all of them.

As long as any Table is has a Check Constraint defined on it or it is referenced in such a constraint, you cannot rename it. That makes perfect sense, otherwise you would break the constraint. If you want to rename a table involved in a Check Constraint, you need to delete the constraint first, then rename your table and finally re-create the constraint with adjusted table or column names.

To delete a Check Constraint, you once again need to resort to a DDL Statement via the ADODB Connection to the CurrentProject. The basic SQL syntax is:

Here it is wrapped in the VBA code to execute the SQL:

Data Macros

Another option for data validation are Data Macros, introduced with Access 2010. Data Macros are similar to Triggers in server-based DBMS like Microsoft SQL-Server. They contain procedural logic that is triggered by data events, e.g. inserting, updating, or deleting records.

I do not have much experience with Data Macros, yet. They would be a topic for a different article anyway, so, for now, I just refer to Microsoft’s article on how to create a data macro.

Update: Video on Validation Rules and Check Constraints

I recently also recorded a video on this topic showing the contents in this text. It is available on my codekabinett/en YouTube Channel and for your convenience I also embed it here.

Share this article: