Microsoft Access Query To Build Relationship

Question

Hi,

I have the following table:

Table Parents with columns ID and FullName. ID is a primary key of type Auto-Number. Table Children with columns ID, ParentID and FullName. ID is a primary key of type Auto-Number. ParentID is of type Number.

I want to use the following query to build a "cascade deletion" so that when a parent is deleted all its children are automatically deleted.

I put the following Query in Access:

ALTER TABLE CHILDREN ADD CONSTRAINT ConstraintName FOREIGN KEY (PARENTID) REFERENCES PARENTS(ID) ON DELETE CASCADE

However, it doesn't work. I get the following error message: "Syntax Error in Constraint Clause" and the "DELETE" word get highlighted.

Any help would be greatly appreciated.

Regards, Seb

Answer 1

I believe that it should be ON DELETE CASCADE not ON CASCADE DELETE

Answer 2

Certain DDL queries will not work in the query window, you need to use VBA and the connection:

s = "ALTER TABLE CHILDREN ADD CONSTRAINT ConstraintName FOREIGN KEY (PARENTID) " _
& "REFERENCES PARENTS(ID) ON DELETE CASCADE"
CurrentProject.Connection.Execute s