SQL Topics: Deleting a Record

	Deleting a Record

Description

To programmatically delete a record using SQL, you combine the DELETE operator in the following primary formula:

DELETE FROM TableName

When this statement is executed, all records from the TableName table would be deleted. Here is an example:

DELETE FROM Customers

In this case, all customer records from a table named Customers in the current database would be deleted. An alternative to the above formula is:

DELETE *
FROM TableName

Here is an example:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE * FROM Videos;"
End Sub

If you execute this type of statement, all records from the table would be deleted. We saw above that the user can specify what particular record to delete instead of all records. You also can specify what record to remove from a table. To do this, use the following formula of the DELETE operator:

DELETE *
FROM TableName
WHERE Condition

This time, the Condition factor allows you to set the condition that would be applied to locate the record. Here is an example of specifying a condition to delete a record:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE * " & _
                 "FROM Videos " & _
                 "Director = 'Adrian Lynn';"
End Sub