ON DELETE CASCADE

[Jens]

Hi there,

What is wrong here ??
ALTER TABLE TRefStubData

ADD CONSTRAINT TRefStubDataTData

FOREIGN KEY (nDataID) REFERENCES TData (nDataID)

ON DELETE CASCADE

I try to alter a table to set a foreign key and some constraint. Access
complaints about this statement with "Syntax error in CONSTRAINT clause".

I found an article in MSDN "Intermediate Microsoft Jet SQL for Access 2000"
which uses pretty much the same statement.

In anther article "HOWTO: Common DDL SQL for the Microsoft Access Database
Engine", they mention it is not possible to use "ON DELETE CASCADE" using
DDL.
snip..
Note: You cannot specify that you want "Cascade Updates" or "Cascade
Deletes" with a relationship created using DDL.
snip end

Is there something wrong in my alter table statement?? Or is it just not
possible to use "ON DELETE CASCADE"?

does anyone have a suggestion on how to alter a MS-access table using ODBC.

Thanks

 

[Roger Carlson]

I think it is pretty clear that you cannot use Access SQL DDL to set up
cascade deletes. However, you can use DAO to do this. Be sure you set a
reference to DAO if using Access 2000 or XP. Here's an example that create
the relation with cascade deletes all in DAO:

Sub exaRelations()
'DAO DDL example
'creating a Relationship
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb

' Create relation
Set rel = db.CreateRelation("PublisherRegions", _
"PUBLISHERS", "SALESREGIONS")

' Set referential integrity w/ casc delets
rel.Attributes = dbRelationDeleteCascade

' Specify key field in KeyTable (Publishers)
Set fld = rel.CreateField("PubID")

' Specify foreign key in ForeignTable (SalesRegions)
fld.ForeignName = "PubIDD"

' Append field to Relation
rel.Fields.Append fld

' Append relation to Relations collection
db.Relations.Append rel

End Sub

 

[Tim Ferguson]

I found an article in MSDN "Intermediate Microsoft Jet SQL for Access
2000" which uses pretty much the same statement.

In anther article "HOWTO: Common DDL SQL for the Microsoft Access
Database Engine", they mention it is not possible to use "ON DELETE
CASCADE" using DDL.

I thought this was legal using the ADO Execute method?


Tim F

 

[Van T. Dinh]

IIRC, you canNOT use the Access Interface or DAO code to run (some?) ALTER
TABLE ... ADD CONSTRAINT ... I think this was mentioned in the article you
quoted (Intermediate Microsoft JET SQL).

Try running your SQL using JET OLE DB provider and ADO.