How To Force Query-Based Updates with ADO and MSDASQL (293658)

SUMMARY

When you use ActiveX Data Objects (ADO) with the Microsoft OLEDB Provider for ODBC Driver (MSDASQL), and you try to update a table using a server-side cursor, ADO uses positioned update by default. In this case, the MSDASQL provider calls the ODBC API SQLSetPos function with SQL_UPDATE to update the data. This may be a problem with some ODBC drivers that do not support SQLSetPos for particular cursor types.

This article demonstrates how to force ADO to do a query-based update with a server-side cursor.

NOTE: With client-side cursors, ADO always uses query-based updates.

MORE INFORMATION

To force a query-based update in ADO, do the following:

Set the active connection of the ADO Recordset object.
Set the Query Based Updates/Deletes/Inserts property of the ADO Recordset object to True before opening the recordset.

The following sample demonstrates how to force ADO to use query-based updates using the MSDASQL provider with the SQL Server driver. The code will also work with any other ODBC driver:

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