cascading delete in SQL ?

[Guest]

Hello
I'd like to know if it's possible to set up a relationship as cascade deletes using SQL under MSAccess
I tried "CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n
ON DELETE CASCADE
but it doesn't work. Anyone have the solution
Thank yo

delphin

 

[Gary Walter]

I'd like to know if it's possible to set up a relationship as cascade deletes using SQL under MSAccess.
I tried "CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE"
but it doesn't work. Anyone have the solution ?

Hi delphine,

How are you trying this? What version of Access?


(are you using the ADOX object library through the Jet OLE DB provider)

MSDN example:
Alter the tblInvoices table by adding a fast foreign key constraint
establishing data consistency between tblInvoices and tblCustomers
with the cascade clauses

CurrentProject().Connection.Execute "ALTER TABLE tblInvoices " & _
"ADD CONSTRAINT FK_tblInvoices FOREIGN KEY " & _
"NO INDEX (CustomerID) REFERENCES " & _
"tblCustomers (CustomerID) ON UPDATE CASCADE " & _
"ON DELETE CASCADE", dbFailOnError

 

[Gary Walter]

Hi delphine,

The query designer is using Jet engine
which (I believe) does not support this
SQL.

You will need to perform this SQL through
ADOX.

In Immediate pane of Debug window (Alt + F11,
or CTRL-G), type

CurrentProject().Connection.Execute "your example SQL"

replacing "your example SQL" with Help example string.

It should work then because it is operating under ADOX,
not Jet.

If it does not work, then at top of Debug window,
select "Tools/References" and make sure you have
referenced correct ADO library.

In fact, I type it using directly MSAccess. I'm at the conception phase and I

simulate user activity. I do not want to use a programmation language to fix
constraints (it's for a lesson). Are you sure that your example works because help of
MSAccess propose an example too, but when you try to paste and copy it, there's an
syntax error !

 

[Guest]

Ok. thanks a lot for your help
delphine

 

[Gary Walter]

Bad wording.....

They are both using "Jet 4.0"
just in importantly-different contexts!

Sorry

"Gary Walter" wrote