Deleting Joined records
Is it possible to delete joined records from multiple tables in a single SQL statement for MS Access?
DELETE clients.*, bookings.* FROM clients, bookings WHERE clients.clientid=bookings.clientid and clients.clientid = 777
This would seem to be the logical syntax, but it doesn't work.
DELETE clients.*, bookings.* FROM clients INNER JOIN bookings On clients.clientid=bookings.clientid WHERE clients.clientid = 777
Obviously I can write two delete statements one for each record based on the [clientid] field, but this strikes me as a messy solution.
This may not help you but I'll give it a go.
Does MS access support foreign key constraints? If you have them set up correctly and you delete the parent records, the db will automatically remove the child records in the other table. this should mean that you only need to
DELETE [name_of_pk] FROM clients cl
WHERE cl.clientid = 777
It would seem Access doesn't support foreign key constraints, so with your code it just orphans the booking information.
*UPDATE* WAIT actually it might, just not by default, you have to explicitly set it in the relationships... this might just work..