Delete from more than one table pls

[looneytunes]

i have 3 linked tables. (invoice, customer, cars)
i have a form showing details from all three tables.
i want to be able to press a delete button whereby the records in all three table will be deleted.
i have managed to do it for one table - however, it doesnt delete the records in other tables.

as suggested in other threads, i have tried ticking the cascade delete, however, doesnt seem to work. also tried the join query, no solution there either.
i would be grateful for any help
thanks as ever

 

[Crilen007]

Cascade delete is the best way.

 

[RV]

Is this on a LIVE environment?
If so, do you store any backup data regarding your invoices?
If your database is your actual backup, you shouldn't delete invoices as you're required by law to store them....

RV

 

[Crilen007]

I think its incase he makes a mistake on one..

 

[yhgtbfk]

i have 3 linked tables. (invoice, customer, cars)
i have a form showing details from all three tables.
i want to be able to press a delete button whereby the records in all three table will be deleted.
i have managed to do it for one table - however, it doesnt delete the records in other tables.

as suggested in other threads, i have tried ticking the cascade delete, however, doesnt seem to work. also tried the join query, no solution there either.
i would be grateful for any help
thanks as ever

Use visual basic.

Have your own delete button, and when you click it, make code for it to delete from all three tables.

DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE FROM table1 WHERE xxx = '" & myfield & "'")
DoCmd.RunSQL ("DELETE FROM table2 WHERE xxx = '" & myfield & "'")
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings True

I hope this is what you mean.

 

[looneytunes]

thanks for all the replies. greatly appreciate it.
after thinking about it more, i will need to keep the invoices as mentioned above.
however, is there a way to delete invoices after 3 years automatically?
i greatly appreciate ur help.
thank you

 

[Crilen007]

Make a delete query that compares the date.


first pull out the year

InvYear = Year(InvDate)


then for the criteria

<(Year(date())-3)


I think thats right.. Anyone wanna verify that?

I will in a sec. Ill test it.

 

[looneytunes]

hi thanks very much it works great. but one problem.

i want it to delete details of the car, customer and invoice. at the moment it will only delete invoice. if i try and include other fields from cars and cust. tables, it says "specify the table containing the records you want to delete"
i would be grateful if u could point me in the right direction. thanks once again

 

[Crilen007]

Set up cascade delete properly.

Honestly that is the best way.

Figure out whats wrong with that.

Perhaps zip and upload or send me your database?

 

[looneytunes]

thanks for ur reply. however,my dbase is fairly big to upload, nearly 9MB.
i have tried the cascade delete option. i even applied it to all my relationships! i cant get it to work. is there any other way? or can anyone hazard a guess as to what i am doing wrong?
at the moment, i am going to the relationships window and double clicking on the link. they already have enforced referencial intergrity. and i am ticking the cascade delete related records.
i greatly appreciate ur help Crilen007

 

[Pat Hartman]

or can anyone hazard a guess as to what i am doing wrong?

My guess is that your delete query is incorrect. I don't know what the hierarchial relationship truely is but I'm going to guess - Customer-Car-Invoice. Invoice, Customer, Cars simply doesn't make any sense to me. So to avoid confusion, I'm going to call the tables tbl1, tbl2, and tbl3. When you want to delete a row from tbl3, use a query that includes only tbl3 or that includes all the tables in the hierarchy. When you want to delete a row from tbl2 and all its children in tbl3, use a query that includes ONLY tbl2 or Only tbl1 and tbl2. The query must NOT include a join to tbl3. To delete a record from tbl1 and all of its children, the query may ONLY include tbl1. As you can see, the "lowest" level table in the delete query is the table from which rows will be deleted.

NEVER code something that the database engnie will do for you. Cascade delete is the correct RDBMS solution.

 

[Crilen007]

9 megs is small, and databases compress very well.

Email it to me at jeremy atttttt lordco dotttttttt com


(format the email properly tho, I did that to stop spam).

 

[looneytunes]

thanks Pat and Chilen007

i think i'm getting there now. if i have probs, i'll let u know!
Cheers