Knowledge Base

PRB: Appending Fields to a Recordset Generates an Error

PSS ID Number: 223771

Article Last Modified on 8/23/2001


The information in this article applies to:

This article was previously published under Q223771

SYMPTOMS

If you try to append Fields to an opened Recordset, you receive the following run-time error:
Run-time error '3219':
The operation requested by the application is not allowed in this context.
In ActiveX Data Objects version 2.7, you receive the following run-time error:
Run-time error '3219':
Operation is not allowed in this context.

CAUSE

From the ADO Help:

"Calling the fields.Append method for an open Recordset or a Recordset where the ActiveConnection property has been set, will cause a run-time error. You can only append fields to a Recordset that is not open and has not yet been connected to a data source. Typically, these are new Recordset objects that you create with the CreateRecordset method or by explicitly assigning a new Recordset object to an object variable."

RESOLUTION

There are two workarounds to getting additional Fields appended to a Recordset object.

  1. Create the Recordset from scratch, creating all the necessary Fields, then open the custom recordset and populate it with the necessary data:
    Dim rst As New ADODB.Recordset
    
    rst.Fields.Append "xx1", adInteger
    rst.Fields.Append "xx2", adChar, 5
    rst.Open
    
    For Each ofld In rst.Fields
        Debug.Print ofld.Name
    Next
    					
  2. Use the Shape Provider Service to append the additional Fields on at the time the recordset is open. This sample uses the Biblio.mdb:
    Dim rst As New ADODB.Recordset
    
    rst.Open "SHAPE {SELECT author FROM authors}" & _
             "APPEND NEW adChar(10) NewCol1, NEW adChar(10) NewCol2", _
             "Provider=MSDATASHAPE" & _
             ";Data Provider=MSDASQL" & _
             ";Data Source=<your_dsn>" & _
             ";User ID=;Password=;", _
             adOpenStatic, adLockBatchOptimistic
    
    For Each ofld In rst.Fields
       Debug.Print ofld.Name
    Next
    
    rst.AddNew Array("author", "NewCol1", "NewCol2"), _
               Array("Detroit", "string1", "string2")
    
    rst.MoveFirst
       While rst.EOF <> True
       Debug.Print rst!author, rst!NewCol1, rst!NewCol2
       rst.MoveNext
    Wend
    					

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Visual Basic and select a New Standard EXE Project. Form1 is created by default.
  2. Add the following Project Reference: Microsoft ActiveX Data Objects Library

  3. Pass the following code in the Load Method of the default Form. This sample uses the Biblio.mdb:
    Dim rst As New ADODB.Recordset
        
    rst.Open "SELECT * FROM authors", _
             "Provider=MSDASQL;Data Source=<your_dsn>;User ID=;Password=;", _
              adOpenStatic, adLockBatchOptimistic
              
    rst.Fields.Append "xx1", adInteger
    					
  4. Run the project.

Additional query words: column

Keywords: kbDatabase kbDSupport kberrmsg kbprb KB223771
Technology: kbADO200 kbADO201 kbADO210 kbADO250 kbADO260 kbADO270 kbADOsearch kbAudDeveloper