PRB: Calculated Field Contents Cannot be Modified by ADO
Trying to modify the contents of a calculated field within an ActiveX Data Objects (ADO) recordset, generates the following error:
Runtime error '-2147217887(80040e21)' errors occurred.
Each calculated field in an ADO recordset contains the following attributes:
Here are two ways to work around this behavior:
This behavior is by design.
NOTE: Setting the adFldUnknownUpdatable and adFldUpdatable attribute flags is provider dependent. If the provider does not set the flags as indicated previously, you get a run-time error when you try to save the record.
The sample code below uses the publishers table in the pubs database that ships with SQL Server.
Option Explicit Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset Private Sub Form_Load() ' Connection String to your SQL Server con.ConnectionString = "Provider=SQLOLEDB.1;Data Source=sequel;User ID=sa;Password=;Initial Catalog=pubs;" con.Open rs.CursorLocation = adUseClient ' Concatenate a character 'A' onto the value retrieved from the field pub_name. rs.Open "SELECT pub_name + 'A' AS PN FROM Publishers", con, adOpenStatic, adLockBatchOptimistic, adCmdText MsgBox rs.RecordCount Debug.Print rs(0).Attributes And adFldUnknownUpdatable, _ rs(0).Attributes And adFldUpdatable rs(0) = "Hello" ' <----- ERROR OCCURS HERE MsgBox "Passed!" End Sub
For more information on how to use the Shape command to append a field to an ADO recordset, please refer to the following article in the Microsoft Knowledge Base:
Q223771 PRB: Appending Fields to a Recordset Generates an Error
© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ammar Abuthuraya, Microsoft Corporation
Additional query words: error -2147217887(80040e21)
Keywords : kbADO kbDatabase kbVBp600 kbGrpDSVBDB kbGrpDSMDAC kbDSupport kbADO210sp2 kbCodeSnippet kbMDAC260 kbADO260
Issue type : kbprb
Last Reviewed: January 9, 2001