Deleting Records

Deleting records using ADO is somewhat similar to updating records in that you need to use SQL queries to identify the record for updating—in this case, deleting. The numbered steps identify a typical ADO algorithm for deleting a record:

1. Declare a new Recordset object variable.

2. Assign updatable cursor and lock types for deleting a record.

3. Construct a dynamic SQL string that uses a condition to retrieve the record selected by the user. The condition should use a field, which is a key (unique) value selected by the user.

4. Open the recordset, which should contain only one record (the record you wish to delete).

5. If the record was found, call the Recordset object's Delete method.

6. Save the record operation using the Recordset object's Update method.

7. Close the recordset using the Recordset object's Close method.

8. Refresh other recordsets, if applicable, by closing and reopening the recordset or calling the Recordset object's Requery method.

Using these steps, I can implement ADO program code in the Click event procedure of the Delete command button shown in Figure 9.2.

Private Sub cmdDelete_Click() Dim sql As String

Dim rsDelete As New ADODB.Recordset

On Error GoTo DbError

'Build dynamic SQL statement based on 'record selected by the user.

sql = "select * from Categories where CategorylD = " & Val(Me.txtCategoryld.Value)"

'Assign updatable cursor and lock type properties. rsDelete.CursorType = adOpenDynamic rsDelete.LockType = adLockOptimistic

'Open the Recordset object.

rsDelete.Open sql, remoteConnection, , , adCmdText

'Don't try to delete the record, if the 'recordset did not find a row. If rsDelete.EOF = False Then

'Update the record based on input from the user. With rsDelete .Delete .Update .Close End With

End If

MsgBox "Record deleted.", vblnformation

'Close the form-level Recordset object and refresh 'it to include the newly updated row. rsCategories.Close SetRecordset

Exit Sub


MsgBox "There was an error deleting the record." _ & Err.Number & ", " & Err.Description

End Sub

0 0

Post a comment