Adding fields to an ADO recordset ?

[jbart]

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.

[dcarlson]

I know you can add fields to a recordset using:

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

You can try this.

[jbart]

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.

[dcarlson]

You may need to persist the recordset.

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

Maybe reopen it and append fields

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.

[jbart]

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.

[dcarlson]

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

    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.

[jbart]

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 !!

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

[dcarlson]

I hate to say this but after some further reading here's what I came across in the ADO 2.1 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.

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?