Append columns to an ADO Recordset

Question

I am attempting to add columns to an ADO recordset in VB6. I simply want to add 4 columns to the end of the table. It's a table we use constantly but we often delete all the data in it and refill it with the next information we want, basically just using it as a means to temporarily hold data.

I have found that since it is ADO I should be able to use the following:

 with rs
     .fields.append "column name", "enum dataType"
 end with

From reading and experimentation it seems that the recordset has to be closed in order to add the columns.

Here's my code:

rs.Open "MeterReads", DataEnvironment7.cnPTracker, adOpenStatic, adLockOptimistic, adCmdTable
' 2019-11-4 Adding in a section to accomadate for days to depeletion
If gbEnableD2D Then
    bExists = False
    With rs
        For Each fField In rs.Fields
            If UCase(fField.Name) = UCase("eddB") Then
                bExists = True
                Exit For
            End If
        Next
        If bExists = False Then
            .Close
            .Fields.Append "eddB", adDate
            .Fields.Append "eddC", adDate
            .Fields.Append "eddM", adDate
            .Fields.Append "eddY", adDate
            .Open
        End If
    End With
End If

I would expect there to be additional columns added to my table. However, I can look at the table and see they aren't in there. I can see as I have closed the recordset. That it attempts to append the columns to it. However, I open the recordset back up and those columns disappear from the table. When I say they appear I mean using Microsoft Visual Basic's debug system. It basically has a section where it shows your variables and for objects it shows you the items inside and a bit more info. So I know it attempts it. But I cant seem to get it to retain it. Any thoughts or ideas on were I'm screwing up would be great.

Answer 1

One approach would be to add the columns when you open the recordset by modifying how you retrieve the data. Instead of using rs.Open with adCmdTable, use adCmdText with a SELECT statement.

SELECT *, NULL AS eddB, NULL AS eddC, NULL AS eddM, NULL AS eddY FROM MeterReads