PRB: ADO adMarshalModified Causes Problem with Late Binding (229654)

MORE INFORMATION

1. Create a new Visual Basic 6.0 Standard EXE and add a reference to the Microsoft ActiveX Data Objects Library.
2. Place a CommandButton on the form and place the following code behind the CommandButton.
   
   Note You must change User ID=<username> to the appropriate username before you run this code. Make sure that the corrected UID has the appropriate permissions to perform the required operations on the specified database.

   Private Sub Command1_Click()
       
       Dim rs As ADODB.Recordset
       Dim x As Long
       Const Connect = "Provider=MSDASQL.1;Persist Security Info=False;User ID=<username>;Data Source=YOUR_DSN;Initial Catalog=pubs"
       Const Query = "SELECT * FROM AUTHORS"
           
       Debug.Print "--------EARLY BINDING-------------"
       Dim obj As ADOMarshalTest.Class1
       Set obj = New ADOMarshalTest.Class1
           
       Set rs = obj.Get_Disconnected_RS(Connect, Query)
       
       rs.MoveFirst
       rs(1).Value = "mike"
       rs(2).Value = "ruth"
       rs.Update
       
       rs.MarshalOptions = adMarshalModifiedOnly
       Debug.Print "Recordset Count Before Update: " & rs.RecordCount
       
       If (obj.UpdateBatchX(Connect, rs) <> 0) Then
           Debug.Print "ERROR ON UPDATE"
           Exit Sub
       End If
           
       Debug.Print "Recordset Count After Update: " & rs.RecordCount
       Set obj = Nothing
       
       Debug.Print "--------LATE BINDING-------------"
       Dim obj2 As Object
       Set obj2 = CreateObject("ADOMarshalTest.Class1")
       
       Set rs = obj2.Get_Disconnected_RS(Connect, Query)
       
       rs.MoveFirst
       rs(1).Value = "Mike"
       rs(2).Value = "Ruth"
       rs.Update
       
       'Comment this line to preserve the recordset on the client
       rs.MarshalOptions = adMarshalModifiedOnly
       
       Debug.Print "Recordset Count Before Update: " & rs.RecordCount
       
       If (obj2.UpdateBatchX(Connect, rs) <> 0) Then
           Debug.Print "ERROR ON UPDATE"
           Exit Sub
       End If
           
       Debug.Print "Recordset Count After Update: " & rs.RecordCount
   
   End Sub
   					

3. Create a new Visual Basic ActiveX EXE project and add a reference to the Microsoft ActiveX Data Objects Library. Rename the project name to ADOMarshalTest.
4. Add the following code to the default class (Class1)in the project:

   Option Explicit
   Public Function UpdateBatchX(ByVal szConnect As String, ByVal rs As ADODB.Recordset) As Long
   
       On Error GoTo errhandler
       
       Dim cn As ADODB.Connection
           
       Set cn = New ADODB.Connection
       With cn
           .ConnectionString = szConnect
           .ConnectionTimeout = 15
           .CursorLocation = adUseClient
           .Open
           
       End With
       
       With rs
           .ActiveConnection = cn
           .UpdateBatch adAffectAllChapters
           
       End With
       
       UpdateBatchX = 0
       Exit Function
   
   errhandler:
       UpdateBatchX = Err.Number
       
   End Function
   
   Public Function Get_Disconnected_RS(ByVal szConnect As String, ByVal SQL As String) As ADODB.Recordset
   
       On Error GoTo errhandler
   
       Dim cn As ADODB.Connection
       Dim rs As ADODB.Recordset
       
       Set cn = New ADODB.Connection
       With cn
           .ConnectionString = szConnect
           .ConnectionTimeout = 15
           .CursorLocation = adUseClient
           .Open
           
       End With
       
       Set rs = CreateObject("ADODB.Recordset")
       rs.Open SQL, cn, adOpenStatic, adLockBatchOptimistic
       Set rs.ActiveConnection = Nothing
       Set Get_Disconnected_RS = rs
       Exit Function
   
   errhandler:
       Set rs = Nothing
       Err.Raise Err.Number, "mrADOComponent..Get_Disconnected_RS", Err.Description
          
   End Function
   					

5. Compile this project into an ActiveX EXE.
6. Return to the first project and set a reference to the newly-created ActiveX component (ADOMarshalTest).
7. Run the client code and note that when late binding is used after the recordset has returned from the server, only the modified records are visible. This problem can be avoided by commenting the line of code that sets the adMarshalModifiedOnly option.