Delete Method

Applies To Dynamic-Type Recordset object, Dynaset-Type Recordset object, Fields collection, Forward-Only–Type Recordset object, Groups collection, Indexes collection, Properties collection, QueryDefs collection, Recordset object, Relations collection, Snapshot-Type Recordset object, Table-Type Recordset object, TableDefs collection, Users collection, Workspaces collection.

Description

Recordset objects — deletes the current record in an updatable Recordset object. For ODBCDirect workspaces, the type of driver determines whether Recordset objects are updatable and therefore support the Delete method.
Collections — deletes a persistent object from a collection.

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.

(continued)

Object	Collection	Can you use the Delete method?

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.

See Also AddNew method, Append method, Close method, Count property, Name property, Refresh method.

Example

This example uses the Delete method to remove a specified record from a Recordset. The DeleteRecord procedure is required for this procedure to run.

Sub DeleteX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim lngID As Long

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    ' Add temporary record to be deleted.
    With rstEmployees
        .Index = "PrimaryKey"
        .AddNew
        !FirstName = "Janelle"
        !LastName = "Tebbs"
        .Update
        .Bookmark = .LastModified
        lngID = !EmployeeID
    End With

    ' Delete the employee record with the specified ID
    ' number.
    DeleteRecord rstEmployees, lngID

    rstEmployees.Close
    dbsNorthwind.Close

End Sub

Sub DeleteRecord(rstTemp As Recordset, _
    lngSeek As Long)

    With rstTemp
        .Seek "=", lngSeek
        If .NoMatch Then
            MsgBox "No employee #" & lngSeek & " in file!"
        Else
            .Delete
            MsgBox "Record for employee #" & lngSeek & " deleted!"
        End If
    End With

End Sub

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
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!Employees
    ' Create new Field object.
    Set fldInitial = tdf.CreateField("MiddleInitial", dbText, 2)
    ' Append new Field object.
    tdf.Fields.Append fldInitial
    ' Refresh Fields collection.
    tdf.Fields.Refresh
    ' Delete new Field object.
    tdf.Fields.Delete fldInitial.Name
    tdf.Fields.Refresh
    Set dbs = Nothing
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 the record. (In Windows NT, Product.dbf is located in the C:\Windows\Msapps\Msquery folder.)

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("C:\Program Files\Common Files" _
    & "\Microsoft Shared\MSquery", 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