MarshalOptions Property Example

This example uses the MarshalOptions property to specify what rows are sent back to the server—All Rows or only Modified Rows.

Public Sub MarshalOptionsX()

   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String
   Dim strOldFirst As String
   Dim strOldLast As String
   Dim strMessage As String
   Dim strMarshalAll As String
   Dim strMarshalModified As String
   
   ' Open recordset with names from Employee table.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.CursorLocation = adUseClient
   rstEmployees.Open "SELECT fname, lname " & _
      "FROM Employee ORDER BY lname", strCnn, , , adCmdText

   ' Store original data.
   strOldFirst = rstEmployees!fname
   strOldLast = rstEmployees!lname
   
   ' Change data in edit buffer.
   rstEmployees!fname = "Linda"
   rstEmployees!lname = "Kobara"

   ' Show contents of buffer and get user input.
   strMessage = "Edit in progress:" & vbCr & _
      " Original data = " & strOldFirst & " " & _
      strOldLast & vbCr & " Data in buffer = " & _
      rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
      "Use Update to replace the original data with " & _
      "the buffered data in the Recordset?"
   strMarshalAll = "Would you like to send all the rows " & _
               "in the recordset back to the server?"
   strMarshalModified = "Would you like to send only " & _
               "modified rows back to the server?"
 
   If MsgBox(strMessage, vbYesNo) = vbYes Then
      If MsgBox(strMarshalAll, vbYesNo) = vbYes Then
         rstEmployees.MarshalOptions = adMarshalAll
         rstEmployees.Update
      ElseIf MsgBox(strMarshalModified, vbYesNo) = vbYes Then
         rstEmployees.MarshalOptions = adMarshalModifiedOnly
         rstEmployees.Update
      End If
   End If
   
   ' Show the resulting data.
   MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
      rstEmployees!lname

   ' Restore original data because this is a demonstration.
   If Not (strOldFirst = rstEmployees!fname And _
         strOldLast = rstEmployees!lname) Then
      rstEmployees!fname = strOldFirst
      rstEmployees!lname = strOldLast
      rstEmployees.Update
   End If

   rstEmployees.Close

End Sub