Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Deleting a record in multiple tables 1

Status
Not open for further replies.

dots

Technical User
Jul 13, 2001
24
US
I have a "Main" table that is related to six other tables by the PropertyID. I'd like to have a delete button on my form that would delete not only the record in the Main table, but also in the other 6 related ones.

Any ideas on how to do this????
 
referential integrity. check out releationships and cascade delete under access help.
 
If you set the relationships of the main table to the 6 related tables to "Cascade deleted records" Access will do this automatically when you delete the main record.

This is NOT a recommended answer, simply an answer to the question. Allowing that property can cause havoc if a user is not careful what they are deleting. Imagine someone deleting your biggest customer from the db by accident. Along with deleting the customer would go their orders, payments, EVERYTHING related to them.

A more safe way to do it is to run SQL statements to delete the related records before deleting the main record with a DoCmd.RunSQL statement like this:

DoCmd.SetWarnings False 'Turn off confirmation message
DoCmd.RunSQL "Delete from MyTable Where ID = "XyZ"
DoCmd.SetWarnings True 'Turn on confirmation message

HTH

Joe Miller
joe.miller@flotech.net
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top