Deleting a row from a recordset that joins 2 tables

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

I'm confused about what happens when I do a delete of a row in a recordset
that joins 2 tables. Does this attempt to delete rows from both tables or
only one table? I'm getting an error indicating it is trying to delete a row
from a table I dont want it to. Here is my code:

rs2.Open "SELECT TD.DTID, TD.DTValue, DT.DataType FROM tblTicketData As TD"
& _
" INNER JOIN tblTicketDataType As DT" & _
" ON TD.DTID = DT.ID WHERE TD.TicketID = " & Me!TicketNo, _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

When I do rs2.delete I want to remove the row from tblTicketData not from
tblTicketDataType. I'm getting an error complaining that it cannot delete
from tblTicketDataType due to a relationship with another table (which is
correct).
 
How is Access to know which table you want to delete from if there are two in
the query?

If you only want to delete from tblTicketData I would suggest:

SQL = "DELETE FROM tblTicketData WHERE dtid IN" _
& " (SELECT id FROM tblTicketDataType WHERE TicketID = " & Me!TicketNo & ")"

Currentdb.Execute SQL, dbFailOnError

John
##################################
Don't Print - Save trees
 
Back
Top