delete two record in different table with one sql statement

[johnnychow]

How to delete two related records in two tables with one sql statement, please correct it. Thank you!!!!!



Private Sub delete_Click()
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim sql As String
Set conn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
sql = "delete from Member, Family where [FamilyID] =" & Me!ID
cmd.CommandText = sql
cmd.Execute
End Sub

 

[Galaxiom]

SQL cannot delete from two tables at once. Just use two separate commands.

The only other way to do it is with Cascaded Deletes in the Relationships. When this is turned on the related records from the second table are deleted when the records are deleted in the first table. It should be used with great caution.

BTW all that stuff you have with recordset is completely pointless. Creating the recordset and setting the conn variable doesn't do anything but waste time.

For the command you could simply run:

CurrentDb.Execute sql

I would recommend you also add the dbFailOnError argument to this line so it will let you know if it fails.