Syntax error when defining table with ON DELETE CASCADE

Question

I'm trying to use ON DELETE CASCADE in a FK constraint in MS Access 2007, but I'm getting an error on table definition:

SQL Error: Syntax error in CONSTRAINT clause.

Here's the code for creating the table:

CREATE TABLE Area (
    Id AUTOINCREMENT PRIMARY KEY, 
    AreaType__Id int NOT NULL, 
    Tbl1 text(31) NOT NULL, 
    Tbl2__Id int NOT NULL, 
    CONSTRAINT UK_Area_1 UNIQUE (Tbl1, Container__Id), 
    CONSTRAINT FK_Area_1 FOREIGN KEY (AreaType__Id) REFERENCES AreaType (Id), 
    CONSTRAINT FK_Area_2 FOREIGN KEY (Tbl2__Id) REFERENCES Tbl2 (Id) ON UPDATE CASCADE ON DELETE CASCADE
);

What am I doing wrong? I looked at the Access Help, and my syntax seems correct. I've tried removing the ON UPDATE CASCADE portion, but got the same error. I've also tried using the default PK field for the referenced table (REFERENCES Container instead of REFERENCES Container (Id)), but again got the same error. I also searched SO, but didn't find much useful info for my situation. It's got to be something simple, but I'm not seeing it at the moment.

EDIT

It's worth mentioning, that the table definition was working properly exactly as it is, except without the ON UPDATE CASCADE ON DELETE CASCADE part. Only after adding the CASCADE parts did the error appear.

EDIT 2

In an attempt to pinpoint the problem, here is new test code that demonstrates the error:

THIS WORKS:

CREATE TABLE T1 (Id AUTOINCREMENT PRIMARY KEY);

CREATE TABLE T2 (
    Id AUTOINCREMENT PRIMARY KEY, 
    T1__Id int NOT NULL, 
    CONSTRAINT FK_T2_1 FOREIGN KEY (T1__Id) REFERENCES T1 (Id)
);

THIS GIVES ERROR:

CREATE TABLE T1 (Id AUTOINCREMENT PRIMARY KEY);

CREATE TABLE T2 (
    Id AUTOINCREMENT PRIMARY KEY, 
    T1__Id int NOT NULL, 
    CONSTRAINT FK_T2_1 FOREIGN KEY (T1__Id) REFERENCES T1 (Id) ON DELETE CASCADE
);

Can anyone replicate the error?

Answer 1

Your CREATE statement is valid Access DDL, but must be executed with ADO.

Here is an Immediate window session which demonstrates the problem ...

strSql = "CREATE TABLE T2 (" & vbCrLf & _
"    Id AUTOINCREMENT PRIMARY KEY, " & vbCrLf & _
"    T1__Id int NOT NULL, " & vbCrLf & _
"    CONSTRAINT FK_T2_1 FOREIGN KEY (T1__Id) REFERENCES T1 (Id) ON DELETE CASCADE" & vbCrLf & _
");"

' executing that statement with DAO triggers error 3289,
' "Syntax error in CONSTRAINT clause."
' (CurrentDb.Execute is a DAO Method)
CurrentDb.Execute strSql ' DAO -> fail

' CurrentProject.Connection.Execute is an ADO method,
' so this attempt executes without error
CurrentProject.Connection.Execute strSql ' ADO -> OK

Note if you were trying to execute the statement from the Access query designer, that also uses DAO so would trigger error 3289 too.