CODEWITHSUNDEEP
vb6ado
ruck64
ruck64

Reputation: 155

Append columns to an ADO Recordset

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.

Upvotes: 2

Views: 4256

Answers (3)

Ching Love
Ching Love

Reputation: 1

with rs
     .fields.append "column name", "enum dataType"
 end withrs.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

Upvotes: 0

Brian M Stafford
Brian M Stafford

Reputation: 8868

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

Upvotes: 3

Étienne Laneville
Étienne Laneville

Reputation: 5031

If you want to modify the structure of the table in your database, you can use the Microsoft ADO Ext. 6.0 for DDL and Security library (add it to your Project from the Project menu > References). You can find the table you want to modify by iterating through the Catalog object's Tables collection. First create a Catalog object:

Dim objCatalog As ADOX.Catalog

' Create and Open Catalog
Set objCatalog = New ADOX.Catalog
Set objCatalog.ActiveConnection = DataEnvironment7.cnPTracker

I am assuming DataEnvironment7.cnPTracker is your current ADO Connection object.

Then iterate through Tables:

Dim objTable As ADOX.Table
Dim sTableName As String

sTableName = "Customers"

' Check if Table exists
For Each objTable In objCatalog.Tables

    If objTable.Name = sTableName Then
        ' Table found, return reference
        Exit For
    End If

Next

Then, once you have the table, you can go through the Columns collection to see if it exists:

Dim objColumn As ADOX.Column
For Each objColumn In objTable.Columns

Finally, if you can't find the column, you can add it:

Set objColumn = New ADOX.Column
With objColumn
    .Name = "FieldName"
    .DefinedSize = 200
    .Type = adVarChar
End With

' Append the new field
objTable.Columns.Append objColumn

Upvotes: 5

Related Questions