DELETE

This statement enables you to remove specific rows from a table. It provides greater flexibility than the ADO Delete method because it can delete many rows at once.

Syntax

DELETE [FROM] tablename [WHERE where-expression]

Parameters

tablename

Specifies the name of the table from which to delete rows.

where-expression

Identical to the SELECT WHERE expression. When supplied, only rows that match the expression will be deleted. For more information, see SELECT - Restricted.

Return Values

One of the following error values can be returned:

• E_OUTOFMEMORY
• DB_E_ERRORSINCOMMAND
• DB_E_NOTABLE
• DB_E_BADCOLUMNID
• DB_E_CANTCONVERTVALUE
• DB_E_DATAOVERFLOW

Remarks

If no WHERE clause is included, DELETE removes all rows from the specified table.

The FROM keyword has no function except to provide SQL compatibility.

Example

Dim rs, i
Set rs = CreateObject("adoce.recordset")
rs.open "create table deleteme (a int, b int, c int)"
rs.open "deleteme", "", 1, 3
For i = 1 To 15
rs.Addnew "a", i
Next
MsgBox rs.recordcount
rs.Close
rs.open "delete deleteme where 'a' > 7"
MsgBox "records deleted"
rs.open "deleteme"
MsgBox rs.recordcount
rs.Close
rs.open "drop table deleteme"
Set rs = Nothing