Hi Jamie,

Thanks for taking the time to answer. I googled "SQL DDL access" and found
this site that explains where/how to enter SQL DDL:

http://www.databasejournal.com/featu...le.php/3460771

I wanted to see how this works so I made a Cars table with three entries
matching Tim's example. I pasted your 'code' into the ddl query and tried to
save it and got an error - "Syntax error in field definition" with the curson
then placed on the parenthesis in front of 'VARCHAR'. Removing the
parenthesis resolves that error.

The next error states "Syntax error in ALTER TABLE statement" then
highlights the second 'ALTER'. Removing the first line's semi-colon and
replacing it with a comma and removing the second 'ALTER TABLE' changes the
error to "Syntax error in field definition" and the word ADD highlighted.
Removing 'Cars', 'ADD', and 'CHECK' and typing in CONSTRAINT results in
another error "Syntax error in CONSTRAINT clause" and the parenthesis
highlighted. Removing the parenthesis resolves that error. The next error
is again "Syntax error in CONSTRAINT clause" but now the word 'IN' seems to
be the problem.

Anyway, I ran just the first (modified) clause and it worked but it seems
that no amount of 'tinkering around' and looking in Access' help files is
helping me get the syntax that works (I'm running A2K3 if that means
anything.) for the second clause. I understand that there are variations on
SQL language - is this problem I'm having getting the second clause to run
the result of one of those variations?

Thanks for your help.

"Jamie Collins" wrote:

rpw wrote:
does this 'code' go?

It's SQL DDL i.e. a Standard way of saying, 'Add this column and this
constraint to your table.' You could actually execute this DDL using:

CurrentProject.Connection .Execute "DDL here"

but obviously I'm guessing element names etc.

Isn't it adding a
field to a table and then adding validation rules?

Essentially yes, although a CHECK constraint is more powerful than a
Validation rule e.g. a CEHCK can reference other columns in the same
row and columns in other tables.

Why is this a 'short cut'?

Tim should address this one. AFAIK a row-level CHECK constraint
involves less overhead than a database-level FOREIGN KEY constraint,
however I think Tim's comment was meant pejoratively, so I'm stumped.

Jamie.

--