DDL to create table field validation rule

[tranchemontaigne]

BACKGROUND
I'm in the process of migrating a flat database application to a relational system. As part of this effort I need to build scripts to create a new relational data model and migrate data.

ENVIRONMENT
MS Access 2000

APPROACH
I'm using DDL to define new table structures, migrate data, and drop legacy tables.

PROBLEM
Some fields need table validation. I'm attempting to use the DDL CHECK keyword to implement this validation. Here's a sample DDL script used to create tables. I am experiencing syntax errors associated with the CHECK clause on t10_Refugee_ID.

<quote>
CREATE TABLE t10_Person
(
t10_Person_ID counter PRIMARY KEY,
t10_Record_Num long ,
t10_First_Report_Date date ,
t10_First_Name text(50) ,
t10_Last_Name text(50) ,
t10_Sex text(1) ,
t10_DOB date ,
t10_Age byte ,
t10_Birth_Country_ID long REFERENCES t02_Codes (t02_Codes_ID),
t10_Year_Arrived_In_US int ,
t10_Race_ID long REFERENCES t02_Codes (t02_Codes_ID),
t10_Ethnicity_ID long REFERENCES t02_Codes (t02_Codes_ID),
t10_Refugee_ID byte CHECK (t10_Refugee_ID in(1,2,9) ),
t10_Immigrant_ID byte

)
</quote>

Any assistance if fixing CHECK syntax would be appreciated.

 

[boblarson]

I don't believe you can use DDL for this. I believe you would need to use DAO for that part.

CurrentDb.TableDefs("TableNameHere").Fields("FieldNameHere").ValidationRule = "'Test' OR 'Not'"

or like:

CurrentDb.TableDefs("TableNameHere").Fields("FieldNameHere").ValidationRule = "Between 10 And 20"

 

[tranchemontaigne]

Bob,

Thanks for the reply. It's a shame that MS Access 2000 does not fully support this standard ANSI-92 SQL standard. Here's what I just found

<quote>
Note The check constraint statement can only be executed through the Jet OLE DB provider and ADO; it will return an error message if used though the Access SQL View user interface. Also note that to drop a check constraint, you must issue the DROP CONSTRAINT statement through the Jet OLE DB provider and ADO. Also, if you do define a check constraint: (1) it won't show as a validation rule in the Access user interface (UI), (2) you can't define the ValidationText property so that a generic error message will display in the Access UI, and (3) you won't be able to delete the table through the Access UI or from code until you drop the constraint by using a DROP CONSTRAINT statement from ADO.</quote>

http://msdn.microsoft.com/en-us/library/aa140015(v=office.10).aspx

The article also suggests that the CHECK keyword is only supported in an ALTER TABLE statement (not embedded within CREATE TABLE statement)

 

[boblarson]

By the way, using the same code I gave you, but slightly different you can insert a validation text:

CurrentDb.TableDefs("TableNameHere").Fields("FieldNameHere").ValidationText = Chr(34) & "Please Enter Something Correct" & Chr(34)

(by the way I tested both of these so I know they work).