Delete Method

See Also    Example    Applies To   

Syntax

recordset.Delete

collection.Delete objectname

The Delete method syntax has these parts.

Part Description
recordset An object variable that represents an updatable Recordset object containing the record you want to delete.
collection An object variable that represents a collection from which you are deleting objectname.
objectname A String that is the Name property setting of an object in collection.

Remarks

You can use the Delete method to delete a current record from a Recordset or a member from a collection, such as a stored table from a database, a stored field from a table, or a stored index from a table.

Recordsets

A Recordset must contain a current record before you use Delete; otherwise, a run-time error occurs.

In an updatable Recordset object, Delete removes the current record and makes it inaccessible. Although you can't edit or use the deleted record, it remains current. Once you move to another record, however, you can't make the deleted record current again. Subsequent references to a deleted record in a Recordset are invalid and produce an error.

You can undo a record deletion if you use transactions and the Rollback method.

If the base table is the primary table in a cascading delete relationship, deleting the current record may also delete one or more records in a foreign table.

Note   To add, edit, or delete a record, there must be a unique index on the record in the underlying data source. If not, a "Permission denied" error will occur on the AddNew, Delete, or Edit method call in a Microsoft Jet workspace, or an "Invalid argument" error will occur on the Update method call in an ODBCDirect workspace.

Collections

You can use the Delete method to delete a persistent object. However, if the collection is a Databases, Recordsets, or Workspaces collection (each of which is stored only in memory), you can remove an open or active object only by closing that object with the Close method.

The deletion of a stored object occurs immediately, but you should use the Refresh method on any other collections that may be affected by changes to the database structure.

When you delete a TableDef object from the TableDefs collection, you delete the table definition and the data in the table.

The following table lists some limitations of the Delete method. The object in the first column contains the collection in the second column. The third column indicates if you can delete an object from that collection (for example, you can never delete a Container object from the Containers collection of a Database object).

Object Collection Can you use the Delete method?
DBEngine Workspaces No. Closing the objects deletes them.
DBEngine Errors No
Workspace Connections No. Closing the objects deletes them.
Workspace Databases No. Closing the objects deletes them.
Workspace Groups Yes
Workspace Users Yes
Connection QueryDefs No
Connection Recordsets No. Closing the objects deletes them.
Database Containers No
Database QueryDefs Yes
Database Recordsets No. Closing the objects deletes them.
Database Relations Yes
Database TableDefs Yes
Group Users Yes
User Groups Yes
Container Documents No
QueryDef Fields No
QueryDef Parameters No
Recordset Fields No
Relation Fields Only when the Relation object is a new, unappended object.
TableDef Fields Only when the TableDef object is new and hasn’t been appended to the database, or when the Updatable property of the TableDef is set to True.
TableDef Indexes Only when the TableDef object is new and hasn’t been appended to the database, or when the Updatable property of the TableDef is set to True.
Index Fields Only when the Index object is new and hasn’t been appended to the database.
Database, Field, Index, QueryDef, TableDef Properties Only when the property is user-defined.
DBEngine, Parameter, Recordset, Workspace Properties No