Delete Method

Applies To

Dynaset-Type Recordset Object, Fields Collection, Groups Collection, Indexes Collection, Properties Collection, QueryDefs Collection, Recordset Object, Relations Collection, Table-Type Recordset Object, TableDefs Collection, Users Collection, Workspaces Collection.

Recordset objects — deletes the current record in an open dynaset-type or table-type Recordset object.
Collections — deletes a stored object from a collection.

Syntax

recordset.Delete

collection.Delete objectname

The Delete method syntax has these parts.

Part	Description

recordset	A variable of an object data type identifying an open dynaset-type or table-type Recordset object containing the record you want to delete.
collection	A variable of an object data type identifying a collection.
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, and a stored index from a table.

Recordsets

When you delete records from a recordset, the recordset must contain a current record before you use Delete; otherwise, a trappable error occurs.

In table- and dynaset-type Recordset objects, 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 undelete a record if you use transactions and the Rollback method.

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

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 using 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 shows some limitations on the use of the Delete method. The object in the first column contains the collection in the second column. The third column indicates when, if ever, 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	When you can use Delete method

DBEngine	Workspaces	Never; use the Close method instead
Workspace	Databases	Never; use the Close method instead
Database	Containers	Never
Database	Recordsets	Never; use the Close method instead
Container	Documents	Never

Object	Collection	When you can use Delete method

Index	Fields	On new objects that haven't been appended to the database
QueryDef	Fields	Never
QueryDef	Parameters	Never
Recordset	Fields	Never
Relation	Fields	On new objects that haven't been appended to the database
TableDef	Fields	On new objects that haven't been appended to the database or when the Updatable property of the TableDef is set to True
TableDef	Indexes	On new objects that haven't been appended to the database or when the Updatable property of the TableDef is set to True
Database, Field, Index, QueryDef, TableDef	Properties	When the property is user-defined

See Also

AddNew Method, Append Method, Close Method, Count Property, Name Property, Refresh Method.

Example

This example finds a record in a Recordset and deletes it.

' Find the record you want to delete.
rstPublications.FindFirst "PubNum = 9"
If Not rstPublications.NoMatch Then    ' Check if record found.
    rstPublications.Delete    ' Delete the record.
End If

This example adds and then deletes an Index from a TableDef.

Dim dbsBiblio As Database
Dim tdfAuthors As TableDef
Dim idxAuthorName As Index, fldAuthor As Field
' Open the database.
Set dbsBiblio = DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
' Set the TableDef.
Set tdfAuthors = dbsBiblio.TableDefs("Authors")    
Set idxAuthorName = tdfAuthors.CreateIndex("Author Name")
Set fldAuthor = idxAuthorName.CreateField("Author")
idxAuthorName.Fields.Append fldAuthor    ' Add Field to Index.
' Append Index to TableDef.
tdfAuthors.Indexes.Append idxAuthorName
tdfAuthors.Indexes.Delete "Author Name"    ' Delete the Index.

Example (Microsoft Access)

The following example creates a field in a table and then deletes the field.

Sub DeleteField()
    Dim dbs As Database, tdf As TableDef
    Dim fldInitial As Field, fld As Field
    
    ' Return Database variable pointing to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!Orders
    ' Create new Field object, specifying name, type, and size.
    Set fldInitial = tdf.CreateField("MiddleInitial", dbText, 2)
    ' Append new Field object.
    tdf.Fields.Append fldInitial
    ' Enumerate through Fields collection to find new Field object.
    For Each fld In tdf.Fields
        If fld.Name = "MiddleInitial" Then
            ' Delete new Field object.
            tdf.Fields.Delete fld.Name
            MsgBox "Field deleted."
        End If
    Next fld
End Sub

Example (Microsoft Excel)

This example adds a new record to PRODUCT.DBF (a dBASE IV table located in the C:\Program Files\Common Files\Microsoft Shared\MSquery folder) and then deletes it. (On Windows NT, PRODUCT.DBF is located in the \WINDOWS\MSAPPS\MSQUERY folder.)

Const sourceDir = "C:\Program Files\Common Files\Microsoft Shared\MSQuery"
Dim db As Database, rs As Recordset
Sheets("Sheet1").Activate
Set theID = ActiveSheet.Cells(1, 2)
Set theCategory = ActiveSheet.Cells(2, 2)
theID.Value = 200
theCategory.Value = "BEVR"
Set db = OpenDatabase(sourceDir, False, False, "dBASE IV")
Set rs = db.OpenRecordset("PRODUCT.DBF", dbOpenTable)
rs.AddNew
rs("PRODUCT_ID") = theID.Value
rs("CATEGORY") = theCategory.Value
rs.Update
MsgBox "The new record has been created with " & theID.Value _
    & " and " & theCategory.Value
rs.Move 0, rs.LastModified
rs.Delete
MsgBox "The record you just created has been deleted"
rs.Close
db.Close