How To Force Query-Based Updates with ADO and MSDASQL

Private Sub Command1_Click()
   Dim Cn2 As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim sSQL As String
   Dim sTest As String

   Set Cn2 = New ADODB.Connection
   With Cn2
      .Provider = "MSDASQL"
      .CursorLocation = adUseServer
      
      'LocalServer is the DSN name connecting to backend SQL Server to Pubs Database
      .ConnectionString = "DSN=Localserver;UID=sa;pwd="
      .Open
   End With

   Set rs = New ADODB.Recordset
   With rs
      .CursorType = adOpenStatic
      .LockType = adLockOptimistic
      .CursorLocation = adUseServer
      
      'Note: You need to set the active connection of the recordset
      'before setting the QBU property.
      
      .ActiveConnection = Cn2
      
      sSQL = "SELECT * FROM AUTHORS"
      
      'Set the QBU property of ADO Recordset object to TRUE.
      'This will ensure that instead of positioned update, MSDSQL will use
      'query based update.
      
      .Properties("Query Based Updates/Deletes/Inserts").Value = True
      
      .Open sSQL, , , , adCmdText
   End With

   'This will print all the available ADO Recordset properties.
   'For Each prop In rs.Properties
   '   Debug.Print prop.Name
   '   Next

   rs.MoveFirst   
   If Not rs.EOF Then
       sTest = rs!au_fname
       sTest = "Testing QBU"
       rs!au_fname = sTest
       rs.Update
   End If

   rs.Close
   Set rs = Nothing
   Cn2.Close
   Set Cn2 = Nothing

End Sub