Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: Fields.Append (adding a column does not seem to work)

    Here is the SQL COde:

    The Fields.Append does not seem to work and gives an error sayting the command text is not set for command object!. What do I need to set?

    sSQL3 = "Select Rates.Product, Rates.Region,Rates.TIER from Rates"
    Set rs3 = New ADODB.Recordset
    rs3.ActiveConnection = cn
    With rs3
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open sSQL3
    .Fields.Append "Balance", adDouble, 20, adFldUpdatable - Problem area!!!
    .Fields.Append "WARATE", adDouble, 20, adFldUpdatable
    End With
    rs3.MoveFirst
    Do Until rs1.EOF
    rs1.MoveFirst
    Do Until rs3.EOF
    If (rs1.Fields(0).Value = rs3.Fields(0).Value And rs1.Fields(1).Value = rs3.Fields(1).Value And rs1.Fields(2).Value = rs3.Fields(2).Value) Then
    rs3.Fields(3).Value = rs1.Fields(3).Value
    rs3.Update
    Exit Do
    End If
    rs3.MoveNext
    Loop
    rs1.MoveNext
    Loop
    rs3.MoveFirst
    Do Until rs2.EOF
    rs2.MoveFirst
    Do Until rs3.EOF
    If (rs2.Fields(0).Value = rs3.Fields(0).Value And rs2.Fields(1).Value = rs3.Fields(1).Value And rs2.Fields(2).Value = rs3.Fields(2).Value) Then
    rs3.Fields(4).Value = (rs2.Fields(3).Value)
    rs3.Update
    Exit Do
    End If
    rs3.MoveNext
    Loop
    rs2.MoveNext
    Loop

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I dont believe you can modify a table using an ADODB Recordset. If I remember (I almost never use the ADO libraries), you must use the Command object of the ADO or ADOX library to issue DDL statements.

    If the table is local, you can also use the TableDef object of the DAO library:
    Code:
    Function AddColumn()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("Table1")
        Set fld = tdf.CreateField("NewColumn", dbText, 50)
        tdf.Fields.Append fld
        
    End Function
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •