PRB: Appending Fields to a Recordset Generates an Error

RESOLUTION

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