Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77

    Unanswered: how to add new column to the recordset

    I know I can get recordset from certain table or tables such as:
    strSql = "Select Fund, Deal, [Deal Name] from tbl_irrdeals “
    rs.Open strSql, con, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic

    But my question is I need add one Boolean column which is not related to any table to this recordset.
    Could I use this: strSql = "Select Fund, Deal, [Deal Name], 0 as flag from tbl_irrdeals “

    I heard there is append function: rs.fields.append flag, adboolean but it doesn’t work with me.
    Anybody know how to add the new column to the recordset??? Thanks a lot
    Last edited by huela; 10-14-04 at 18:40.

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    huela,

    Glad to know there is another using ADO rather than DAO. Unfortunately by design intent ADO does not know the infrastructure of Jet.

    However there is the Extension for Security and DDL, 'ADOX'. Underneath the Catalog object you will find all the methods and properties you need for adding another column to your table. Remember to reference this library.

    I hesitate to offer more help because everytime I find myself hereabouts I only get things to work by trial and error. Still you could ignore that and ask anyway.
    Rod

    fe_rod@hotmail.com

  3. #3
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    Thank you very much for help. Appreciate it. Please don't hesitate to answer the questions, because it may give us the different idea how to solve the problem even it's not the right answer.

    use ADOX? Ok, I will try it. What's the name of reference library, do you know? (//blush, because i am new)

    By the way, i want to add column to recordset, but don't add this column to the table. Is it possible?

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    huela,

    Sorry, I answered a slightly different question the first time round. You need ADOX to add a new column to a table but adding a new field to a recordset can be done via ADO. So you don't need to add a new reference. (For future reference the name of ADOX in the list is 'Microsoft ADO Ext. n.n. for DDL and Security'.)

    So sticking with ADO: the Recordset object has a collection called Fields and the Fields collection has a method Append. Here's an extract from the Help

    Append Method

    Appends an object to a collection. If the collection is Fields, a new Field object may be created before it is appended to the collection.

    Syntax

    collection.Append object


    What I suggest you do is instatiate a new Field object and then Append it to the Fields collection in your Recordset. I guess the Type property of your Field should be adBoolean.
    Rod

    fe_rod@hotmail.com

  5. #5
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    huela,

    I've just come across an interesting and I think relevant comment in the on-line Help. It says that the record set bound to an Access form in an .mdb application is always a DAO class recordset.

    The implication for you is that if you are working with a recordset you have explicitly created yourself such as

    Dim rs As New ADODB.Recordset

    then you need to use the ADO methods and properties.

    But if you are working with the recordset behind an Access form, for example

    Set rs = Forms("myform").RecordSet

    then you need to use the DAO methods and properties.

    Is this then a case where it is necessary to reference both the ADO and DAO COM object models in the same application? Anyone out there care to comment?
    Rod

    fe_rod@hotmail.com

  6. #6
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    Rod:

    Thank you very much. I successfully add a new column to the recordset. even i still have other problem.

    But adding new column to recordset is ok now.
    I just share the way i did.
    First i select data i want to recordset1
    then i declare new recordset2, and add all the columns to recordset2
    then insert all data into recordset2.

    My original mistake is, i try to add column into recordset1. since it already have data after i select, so i can't add new column. I can't add before it either, because i have to select data into after i add new column.
    Here is the code

    strSql = "Select Fund, Deal, [Deal Name] from dbo_tbl_irrdeals where " _
    & dealFilter
    With rs
    .Open strSql, Con, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic
    End With

    rscp.Fields.Append "Fund", adVarChar, 6
    rscp.Fields.Append "Deal", adVarChar, 6
    rscp.Fields.Append "Deal Name", adVarChar, 50
    rscp.Fields.Append "selection", adBoolean
    rscp.Open

    For i = 1 To rs.recordCount
    rscp.AddNew _
    Array("Fund", "Deal", "Deal Name", "Selection"), _
    Array(rs.Fields(0).Value, rs.Fields(1).Value, rs.Fields(2).Value, 0)
    rs.MoveNext
    Next

  7. #7
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    I got new questions for my project.

    I have one subform, which can be either bound form or unbound form. it doesn't really matter.
    I got some data from recordset like previous post. and manipulate recordset. After certain action, then decide whether save data into the database.

    I think the good way to do it is by using the disconnected recordset, which use ADO

    but Now, the problem is I don't know how to display the recordset into my form.

    The way display in the bound form: me.recordset = rscp
    doesn't work.
    It give me error: the object you entered is not a valid recordset property

    Is anyone know how to display the disconnected recordset in a form???

Posting Permissions

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