How can I concatenate ADO recordsets

[eamonskelly]

I am looking for a way to concatenate 9 ADO recordsets into one , or to put the queries within them into one recordset. I have select queries, which I need to put into one for use in a report. Is their a simple way to do this.
Thanks for any help

 

[mymou]

Hi eamonskelly

Not sure about concatenation of rs - except through some silly loops.

Would have thought it would be better to use a UNION query or append a temp table in SQL.

Stew

 

[MarkSweetland]

You can return more than one recordset in ADO and itterate through them with the .nextrecordset method. However, these recordsets are distinct and are not linked in anyway. For one complete recordset containing all information, as Stew mentioned, a UNION of all the recordsets (providing the number of columns are the same) or you can use JOINS if there is some common thread between each of the recordsets and only return the information you need for your report.


Mark

 

[eamonskelly]

Thanks for your help, both of you, I have tried both methods, but eventually just used the following (abbreviated) stored procedure, which went through all the selects and inserted them into a table. It went as follows.

BEGIN
Insert Into table item1 , item2, item3
select ----

insert into table item1 , item2, item3
select ---

etc
END