Validation rules in CREATE TABLE statement

[Ach]

Hi, I'm fairly new to Access and I'm trying to figure out how to create a validation rule via SQL. I searched through the forums but didn't find anything. Hope someone can point me in the right direction. For simplicity say I have the following extremely simple CREATE STATEMENT.

CREATE TABLE GAMES (
Name TEXT PRIMARY KEY NOT NULL,
Amount CURRENCY NOT NULL,
);

How could I rewrite the above query to specify that the only valid amounts allowed are say between 1 and 10?
I'm familiar with the Constraint statement and the Check clause but I can't get these to work in Access.

 

[RoyVidar]

Validation rules are an "Access" thingie, and, as far as I know, they can't be written through DDL.

Now the "trickiness" begins. Access is really the front end, which can use several different databases. The native, or default, database is Jet.

The Jet database supports a lot of features that isn't supported through the Access interface, and check constraints is one of them. You can use check constraints in DDL to achieve what you wish, but the downside is that you cannot maintain, or even see these, in the Access interface. You'll also need to execute the DDL on an ADO/OLEDB connection, say

dim SqlString as string
SqlString= "CREATE TABLE GAMES ( " & _
           "TheName TEXT(25) PRIMARY KEY NOT NULL, " & _
           "Amount CURRENCY NOT NULL, " & _
           "CONSTRAINT chk_amount_between_1_and_10 " & _
           "CHECK (Amount Between 1 And 10))"
Currentproject.Connection.Execute SqlString, , adcmdtext+adexecutenorecords

You can also switch SQL compatibility mode to more ANSI-92 like syntax (Tools | Options - somewhere at the bottom right of one of the tabs, if I remember correct). Then you can use more SQL server like syntax in the query tool (Jet SQL, not the Access "dialect").

If I haven't goofed up too much on the typing, the above should create a table with a check constraint preventing entries that aren't between 1 and 10, but if you enter table design, you will find nothing in the validation rule property. Also, if you try to delete this table through the interface, you'll be informed you dannot do so, because apparently the table is referenced in a check constraint *). If this is interesing, you'll find more info here http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx

Perhaps in stead look into the validation rule/validation text properties of Access? They are easier to deal with. You can also set them programatically, see for instance the last reply of Allen Browne here http://groups.google.com/group/micr...esdbdesign/browse_frm/thread/3ed92d465371f28d

Some validation rule samples
http://allenbrowne.com/ValidationRule.html
Some function samples, the "SetPropertyDAO" might be interesting
http://allenbrowne.com/func-DAO.html

Edit:
*) To delete the table, you'd first need to drop the constraint, then you can delete the table

Currentproject.Connection.Execute _
    "ALTER TABLE Games DROP CONSTRAINT chk_amount_between_1_and_10", , _
    adcmdtext+adexecutenorecords
Currentproject.Connection.Execute _
    "DROP TABLE Games", , adcmdtext+adexecutenorecords

 

[Ach]

Wow. I expected a one line response.
Thank you for taking the time to write that out. Very helfpul.