CODEWITHSUNDEEP
sqlms-access
user3431058
user3431058

Reputation: 31

Validation rule in sql and access

I am trying to create a validation rule but using SQL in Access. I am very new to this. I know how to do it in regular Access design view, but don't know how to do it when using the create table command. So far I have,

CREATE TABLE CUSTOMERS
(
    CustomerName TEXT (20),
    Gender TEXT (10),
    FavoriteFood TEXT (20)
);

Would like to add a validation rule to gender for just male/female/notknown.

Upvotes: 3

Views: 6815

Answers (1)

HansUp
HansUp

Reputation: 97101

A field's Validation Rule is a DAO.TableDef property which can't be created using Access SQL.

If you want to use Access SQL for something similar, use a CHECK CONSTRAINT. This statement will limit Gender values to male, female, or unknown.

CREATE TABLE CUSTOMERS
(
    CustomerName TEXT (20),
    Gender TEXT (10) NOT NULL,
    FavoriteFood TEXT (20),
    CONSTRAINT allowed_genders CHECK
        (
            Gender IN ('male','female','unknown')
        )
);

Note a CHECK CONSTRAINT can only be used in Access SQL when executed from ADO. I executed that statement with CurrentProject.Connection.Execute, which is an ADO method.

Some consequences of this approach may make it unacceptable to you ...

  1. The constraint is not visible in table Design View.
  2. When the constraint is violated, the error message does not appear until you attempt to save the record.
  3. And the error message is based on the constraint name ... which is not very user-friendly in this case.
  4. Because of the constraint, you can not delete the table from the Access UI; you would have to first execute DROP CONSTRAINT allowed_genders and then delete the table, or execute DROP TABLE CUSTOMERS which will discard both the table and constraint at the same time.

You might prefer to create a simpler version of the table and then set the Validation Rule with DAO.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Set tdf = db.TableDefs("CUSTOMERS")
tdf.Fields("Gender").ValidationRule = "IN ('male','female','unknown')"
tdf.Fields("Gender").ValidationText = "Gender must be male, female, or unknown"

Notes:

  1. The DAO methods allow you to also include a user-friendly Validation Text message.
  2. When the Validation Rule is violated, Access will display the error message as soon as you attempt to move to the next field.

Finally yet another approach would be to create a lookup table which contains your allowed Gender values, make CUSTOMERS.Gender a foreign key to that lookup field, and enforce referential integrity.

Any of those 3 approaches might fit your situation; choose whichever seems to fit best. :-)

Upvotes: 3

Related Questions