Create a check constraint in access and/or DAO

I am trying to create a check constraint on an access (jet?) table.

So, I open the .mdb file with access, go into queries->create query in design view, type esc, then menu->view->query and fin开发者_如何学Cally type

create table X ( a number, check (a > 20) )

but access thinks that I have a "syntax error in field definition". However, I don't think so. Therefore my question: is it possible to create a check constraint with access. If so: how.

Additionally, I'd like to create the constraint with dao/vba, not on the GUI. Is that possible?

And lastly, on a slightly related note: how do you enter sql statements into access. I can't imagine that I have to use the queries->design view->query->view route in order to do that. I am used to Oracle's SQL*Plus, which I like very much, and I'd hope there's something similar for access as well.

Thanks for any input Rene

Here are some notes.

You can create a Pass-Through query for Oracle (Select menu "Query" > "SQL Specific" > "Pass-Through")

Since Access 2003, you can select SQL Server Compatible Syntax (ANSI 92) (http://office.microsoft.com/en-us/access/HA010345621033.aspx)

A validation rule with VBA / DAO

''Reference: Microsoft DAO x.x Object Library

Dim tdf As TableDef
Dim db As Database

Set db = CurrentDb

Set tdf = db.TableDefs("Table1")

tdf.Fields("aDouble").ValidationRule = "<10"
tdf.Fields("aDouble").ValidationText = "Must be less than 10"

Constraints with ADO / VBA. See [Intermediate Microsoft Jet SQL for Access 2000](http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx)

''Reference: Microsoft ADO Ext. x.x for DDL and Security

Dim cn As ADODB.Connection 'For action queries
Dim rs As ADODB.Recordset  'For select queries
Dim s As String
Dim RecordsAffected As Long

Set cn = CurrentProject.Connection

''You can store sql in a table
s = DLookup("SQLText", "sysSQL", "ObjectName='q1'")
''Result: CREATE TABLE tblCreditLimit (LIMIT DOUBLE)
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''You can run queries from VBA
s = "INSERT INTO tblCreditLimit VALUES (100)"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "CREATE TABLE tblCustomers (CustomerID COUNTER, CustomerName Text(50))"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "INSERT INTO tblCustomers VALUES (1, 'ABC Co')"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "ALTER TABLE tblCustomers " _
   & "ADD COLUMN CustomerLimit DOUBLE"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''You can add contraints using ADO like so:
s = "ALTER TABLE tblCustomers " _
   & "ADD CONSTRAINT LimitRule " _
   & "CHECK (CustomerLimit <= (SELECT LIMIT " _
   & "FROM tblCreditLimit))"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "UPDATE tblCustomers " _
   & "SET CustomerLimit = 200 " _
   & "WHERE CustomerID = 1"
''Error occurs here
cn.Execute s, RecordsAffected

s = "UPDATE tblCustomers " _
   & "SET CustomerLimit = 90 " _
   & "WHERE CustomerID = 1"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''Clean up
''You cannot do this through the database window,
''because of the constraint.
s = "ALTER TABLE tblCustomers DROP CONSTRAINT LimitRule "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "DROP TABLE tblCustomers "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "DROP TABLE tblCreditLimit "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

There is Validation rule on a column. You can use VB for Access. no SQL*Plus here... You can always use SQL Express as a data source - with all the benefits of real sql server and use access only as a front.

To do this in Access, you need to first open the interface into ANSI-92 Query Mode. I've tested your SQL DDL code: it works fine and creates a column of type FLOAT (Double).

Is is not possible to do this using DAO but you can use ADO. Long story short: CHECK constraints were introduced into the engine in the Jet 4.0 era when the Access Team were favouring ADO. With effect from Access2007, the Access Team are back to favouring DAO but have yet to plug the Jet 4.0 'holes' in DAO. So for the majority of Jet 4.0 -only functionality (compressible data types, fixed-length text data types, fast foreign keys, etc) you need to use ADO.

You can’t use standard ANSI in the query builder UNLESS you set the database as sql ansi compatible. If you do change this setting, then you CAN can use the sql in the query builder as you have. I would not recommend changing this setting for existing databases however.

If you do, you could type in:

CREATE TABLE z1 
       (id int IDENTITY , FirstName CHAR, LastName CHAR, SSN INTEGER ,
check (id < 20),  
constraint Mypk primary key (id) )

In you don’t need to save the sql in the query builder, and just want to type in the sql, then simply whack ctrl-g to get the access command line prompt, and you can then type in:

currentproject.Connection.Execute "CREATE TABLE
    z1(id int IDENTITY , FirstName CHAR, LastName CHAR, SSN INTEGER ,
    check (id < 20),
    constraint Mypk primary key (id) )"

The above would be typed on one line. So, you can use the command line prompt if you want..