Delete from multiple tables

[camillaj]

I am trying to delete a tablerow from two tables with a relation. That is, there is a foreign key id relation and I want to delete all records with a certain id.

This wasn't as simple as I though it would be. Is it possible to do it all in one query in Access?

I tried something like:
DELETE * FROM Question INNER JOIN CheckboxAlternatives ON Question.QuestId=CheckboxAlternatives.QuestId
WHERE Question.QuestId=@id And CheckboxAlternatives.QuestId=@id;

But this is not allowed. I want to delete all records with Questid=@id from both tables!

Please, can somebody help me with this?

Thanks a lot

 

[KenReay]

Hi

In your relationship, if you choose Cascade Delete, Acecss will delete the records from the 'many' side automatically

To set Cascade delete, right click on the relationship line in the relationships window, and click the relevant tick box

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK