Update and CancelUpdate Methods Example
This example demonstrates the Update method in conjunction with CancelUpdate method.
Sub UpdateX()
Dim rstEmployees As ADODB.Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
' Open recordset with names from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenDynamic
rstEmployees.LockType = adLockPessimistic
rstEmployees.Open "SELECT fname, lname " & _
"FROM Employee ORDER BY lname", strCnn
With rstEmployees
' Store original data.
strOldFirst = !fname
strOldLast = !lname
' Change data in edit buffer.
!fname = "Linda"
!lname = "Kobara"
' Show contents of buffer and get user input.
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
!fname & " " & !lname & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"
If MsgBox(strMessage, vbYesNo) = vbYes Then
.Update
Else
.CancelUpdate
End If
' Show the resulting data.
MsgBox "Data in recordset = " & !fname & " " & _
!lname
' Restore original data because this is a demonstration.
If Not (strOldFirst = !fname And _
strOldLast = !lname) Then
'.Edit
!fname = strOldFirst
!lname = strOldLast
.Update
End If
.Close
End With
End Sub
This example demonstrates the Update method in conjunction with the AddNew method.
Sub UpdateX2()
Dim rstEmployees As ADODB.Recordset
Dim strEmpID As String
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
' Open recordset with data from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenDynamic
rstEmployees.LockType = adLockPessimistic
rstEmployees.Open "employee", strCnn
With rstEmployees
.AddNew
strEmpID = "B-S55555M"
!emp_id = strEmpID
!fname = "Bill"
!lname = "Sornsin"
' Show contents of buffer and get user input.
strMessage = "AddNew in progress:" & vbCr & _
"Data in buffer = " & !emp_id & ", " & _
!fname & " " & !lname & vbCr & vbCr & _
"Use Update to save buffer to recordset?"
If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
.Update
' Go to the new record and show the resulting data.
MsgBox "Data in recordset = " & !emp_id & ", " & _
!fname & " " & !lname
Else
.CancelUpdate
MsgBox "No new record added."
End If
' Delete new data because this is a demonstration.
.Filter = "emp_id = '" & strEmpID & "'"
.Delete
.Close
End With
End Sub
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.