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.