Microsoft KB Archive/293658

= How To Force Query-Based Updates with ADO and MSDASQL =

Article ID: 293658

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft OLE DB Provider for ODBC 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q293658



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 = &quot;MSDASQL&quot; .CursorLocation = adUseServer 'LocalServer is the DSN name connecting to backend SQL Server to Pubs Database .ConnectionString = &quot;DSN=Localserver;UID=sa;pwd=&quot; .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 = &quot;SELECT * FROM AUTHORS&quot; '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(&quot;Query Based Updates/Deletes/Inserts&quot;).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 = &quot;Testing QBU&quot; rs!au_fname = sTest rs.Update End If

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

End Sub

Additional query words: QBU SQLSetPos

Keywords: kbhowto KB293658

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.