Adding fields to an ADO recordset ?

Hi,

Is it possible to alter the structure of an ADO recordset ? Using FoxPro, I can use a statement such as :

ALTER TABLE tempdirectors ADD COLUMN rfname c(20)

to add a new field to a table.

Any comparable command in VB ?

Thanks for any info.

I know you can add fields to a recordset using:

Code:

---

rstReport.Fields.Append "comp_id",adChar, 6, adFldUpdatable

---

You can try this.

I thought that was the command to use, but I get the run-time error "3219" - Operation is not allowed in this context.

I have SQL statements in the Data Environment to JOIN 2 tables, and then I need to add additional fields for the next join.

I'll keep experimenting with it and see what happens.

Thanks.

You may need to persist the recordset.

Code:

---

rs.Save "c:\yourFile.adtg", adPersistADTG

---

Maybe reopen it and append fields

Code:

---

rs2.open "c:\yourFile.adtg"

rs2.fields.append......

---

I don't know if you want to do that or not but you should be able to append fields.

I was able to get the fields to append if I used a recordset that was built from a single table, not from an SQL statement that JOINed 2 tables. But now I need to determine how to append a recordset to the recordset with the appended fields, and then how to read those fields.

Thanks for your help.

This will give you an idea on how to read the fields in your recordset.

Code:

---

    Dim strSql As String
    Dim fldCurrent As ADODB.Field
    Dim rstCurrent As ADODB.Recordset
   
    strSql = "SELECT * FROM staff"
    Set rstCurrent = New ADODB.Recordset
    rstCurrent.Open strSql, fCnn1, adOpenStatic, , adCmdText
   
    For Each fldCurrent In rstCurrent.Fields
        Debug.Print fldCurrent.Name
    Next

---

What you could do then is create a recordset, then append the fields from both tables that you want then loop through the recordsets to populate your recordset using the rs.addnew method.

Derick,

I tried saving and re-opening the recordset, but the Append still generates the error message "Operation is not allowed in this context".

Any ideas ? Thanks !!

Code:

---

Option Explicit
Dim dbCNN As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset

Private Sub Command1_Click()
    Dim strSQL As String
    Set dbCNN = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
   
    dbCNN.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Property Tax Appraisal"

    strSQL = "SELECT Chief.*, DIRECTOR.honorific AS dhonor,DIRECTOR.fname AS dfname," & _
    "DIRECTOR.INIT AS dinit,DIRECTOR.lname AS dlname,DIRECTOR.TITLE AS dtitle," & _
    "DIRECTOR.street AS dstreet, DIRECTOR.city AS dcity,DIRECTOR.mbrname," & _
    "DIRECTOR.zip AS dzip,DIRECTOR.phone AS dirphone,1 AS printorder " & _
    "FROM  (Chief FULL JOIN DIRECTOR ON  Chief.cad = DIRECTOR.cad) ORDER BY Chief.cad, DIRECTOR.lname"
   
    rs.Open strSQL, dbCNN, adOpenDynamic, adLockOptimistic
   
    rs.Save "c:\savedRS.adtg", adPersistADTG
    rs2.Open "c:\savedRS.adtg"
   
    rs2.Fields.Append "rfname", adChar, 20, adFldUpdatable
   
End Sub

---

I hate to say this but after some further reading here's what I came across in the ADO 2.1 help.

Quote:

---

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.

---

If you know what fields and types are in the query you can create a recordset and add the fields using the append. Then populate the recordset using the addnew and update method.

By the way what's the recordset for?