You can use the ADO "DataShape" function to make one DB call an combine all
your RS into one.





-----Original Message-----
From: jrodrigues@s... [mailto:jrodrigues@s...]
Sent: Thursday, July 26, 2001 12:34 AM
To: professional vb
Subject: [pro_vb] Re: ADO Recordsets "Union"


> I have 5 recordsets with virtually the same data structure.  These are
> created with 5 different SQL strings against AS400 odbc data.  all
fields
> are named that same with alias'.  Is there a way to combine all 5
> recordsets into one distinct recordset besides doing .addnew ???
>
>
> Is there a union function for ADO?
>
>
> Thanks!
>
> David Hay

Hi David,

First sorry for my english, Iīm brazilian.

You could try this sequence:

- Create the new table with all columns of the first recordset following
the recordsetīs field order, and repeat to the others;

- Then you could use something like:

   with rstNewtable
       .AddNew
       for intCont = 1 to rstTB1.fields.count
           .field(intCont-1) = rstTB1.field(intCont-1)
       next intCont

       atupos = rstTB1.fields.count
       for intCont = 1 to rstTB2.fields.count
           .field(atupos+intCont-1) = rstTB2.field(intCont-1)
       next intCont

       atupos = rstTB2.fields.count
       for intCont = 1 to rstTB3.fields.count
           .field(atupos+intCont-1) = rstTB3.field(intCont-1)
       next intCont

       atupos = rstTB3.fields.count
       for intCont = 1 to rstTB4.fields.count
           .field(atupos+intCont-1) = rstTB4.field(intCont-1)
       next intCont

       atupos = rstTB4.fields.count
       for intCont = 1 to rstTB5.fields.count
           .field(atupos+intCont-1) = rstTB5.field(intCont-1)
       next intCont

       .Update
    end with


Donīt forget the begintrans and committrans.

-------

But if you can create another select, you could try this:

- Create a select like:
rstFull.open  "Select TB1.*, TB2.*, TB3.*, TB4.*, TB5.* FROM TB1, TB2,
TB3, TB4, TB5"

- Create the new table with all columns of the first recordset following
the recordsetīs field order, and repeat to the others tables;

- Then try this:

    do while not rstFull.eof
       rstNewTable.AddNew
       for intCont = 1 to rstNewTable.fields.count
            rstNewTable.field(intCont-1) = rstFull.field(intCont-1)
       next intCont
       rstNewTable.Update
       rstFull.MoveNext
    loop


Itīs something like this.

Best Regards,

Juliano Moraes Rodrigues
Analista de Sistemas
FA Consult
São Paulo - Brasil