Delete Method Example (VB)

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

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub DeleteX()

   Dim rstRoySched As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim strSQLRoySched As String
   
   Dim strMsg As String
   Dim strTitleID As String
   Dim intLoRange As Integer
   Dim intHiRange As Integer
   Dim intRoyalty As Integer

    ' open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
   
   ' open RoySched table with cursor client-side
   Set rstRoySched = New ADODB.Recordset
   rstRoySched.CursorLocation = adUseClient
   rstRoySched.CursorType = adOpenStatic
   rstRoySched.LockType = adLockBatchOptimistic
   rstRoySched.Open "SELECT * FROM roysched WHERE royalty = 20", strCnxn, , , 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
'EndDeleteVB
Delete Method Example (VB)

See Also
