Delete orphan records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have two tables named Customers and Contracts related with a one-to-many
relation.
When I delete a contract record I want the relevant customer’s record to
remain if the certain customer has other contracts. However if the certain
customers has no other contract, I want his record automatically erased. In
other words I don’t want to have orphan records in Customers table.
Do you know a way to do it?

Thank you

GL
 
Try something like;


Dim rst as DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) AS Count"
strSQL = strSQL & " FROM Contracts"
strSQL = strSQL & " WHERE CustomerID=" & Trim(Str(lngCustomerID)) & ";"

Set rst = db.OpenRecordset(strSQL, fbOpenForwardOnly)

If rst!Count <= 1 'either 1 or 0 record's exists, then delete current
contract and customer
strSQL = "DELETE * FROM Contracts WHERE CustomerID=" &
Trim(Str(lngCustomerID)) & ";"
db.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM Customers WHERE CustomerID=" &
Trim(Str(lngCustomerID)) & ";"
db.Execute strSQL, dbFailOnErrorEndIf

rst.Close
set rst = Nothing


Assumes that CustomerID is a numeric.

Hope this helps.
 
I assume that the code is in a sub driven by a “Delete command button†of
the form. In this case there is an issue if a record is deleted when the
user clicks the keyboard “Delete†button isn’t it?
If CustomerID is string what is differentiated in the code?
 
The answer to the first point is yes. If the record is being deleted through
the "Delete" button then you could use the Form_Delete event to drive the
deletion of the other records. If CustomerID is a string then instead of
using;

CustomerID=" & Trim(Str(lngCustomerID)) & ";"

you would use something like

CustomerID='" & strCustomerID & "';"

note the single quotes around strCustomerID.
 
Thanks a lot.
Do you think that is also safe to use a query on start up like

DELETE Exists (SELECT CustomerID FROM Contracts WHERE Contracts.CustomerID =
Customers.CustomerID) AS Expr1
FROM Customers
WHERE (((Exists (SELECT CustomerID FROM contracts WHERE Contracts.CustomerID
= Customers.CustomerID))=False));

or it is not neccessary?
 
Back
Top