Delete Method Example (VB)

Delete Method Example (VB)

This example uses the Delete method to remove a specified record from a Recordset.

Public Sub DeleteX()

    Dim rstRoySched As ADODB.Recordset
    Dim strCnn As String
    Dim strMsg As String
    Dim strTitleID As String
    Dim intLoRange As Integer
    Dim intHiRange As Integer
    Dim intRoyalty As Integer

    ' Open RoySched table.
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    Set rstRoySched = New ADODB.Recordset
    rstRoySched.CursorLocation = adUseClient
    rstRoySched.CursorType = adOpenStatic
    rstRoySched.LockType = adLockBatchOptimistic
    rstRoySched.Open "SELECT * FROM roysched " & _
        "WHERE royalty = 20", strCnn, , , adCmdText

    ' Prompt for a record to delete.
    strMsg = "Before delete there are " & _
        rstRoySched.RecordCount & _
        " titles with 20 percent royalty:" & vbCr & vbCr
    Do While Not rstRoySched.EOF
        strMsg = strMsg & rstRoySched!title_id & vbCr
        rstRoySched.MoveNext
    Loop
    strMsg = strMsg & vbCr & vbCr & _
        "Enter the ID of a record to delete:"
    strTitleID = UCase(InputBox(strMsg))

    ' Move to the record and save data so it can be restored.
    rstRoySched.Filter = "title_id = '" & strTitleID & "'"
    intLoRange = rstRoySched!lorange
    intHiRange = rstRoySched!hirange
    intRoyalty = rstRoySched!royalty

    ' Delete the record.
    rstRoySched.Delete
    rstRoySched.UpdateBatch

    ' Show the results.
    rstRoySched.Filter = adFilterNone
    rstRoySched.Requery
    strMsg = ""
    strMsg = "After delete there are " & _
        rstRoySched.RecordCount & _
        " titles with 20 percent royalty:" & vbCr & vbCr
    Do While Not rstRoySched.EOF
        strMsg = strMsg & rstRoySched!title_id & vbCr
        rstRoySched.MoveNext
    Loop
    MsgBox strMsg

    ' Restore the data because this is a demonstration.
    rstRoySched.AddNew
    rstRoySched!title_id = strTitleID
    rstRoySched!lorange = intLoRange
    rstRoySched!hirange = intHiRange
    rstRoySched!royalty = intRoyalty
    rstRoySched.UpdateBatch

    rstRoySched.Close

End Sub