Delete Method
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 |