ON DELETE CASCADE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jens - 24 Jun 2004 02:21 GMT

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

Reply to this Message

Roger Carlson - 24 Jun 2004 14:08 GMT

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

Signature

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

> Hi there,
>
[quoted text clipped - 27 lines]
>
> Thanks

Reply to this Message

Tim Ferguson - 24 Jun 2004 22:50 GMT

> 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

Reply to this Message

Van T. Dinh - 25 Jun 2004 04:50 GMT

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.

Signature

HTH
Van T. Dinh
MVP (Access)

> Hi there,
>
[quoted text clipped - 27 lines]
>
> Thanks

Reply to this Message