Append rows to a Recordset instead of using Union All statement

[news.microsoft com]

Hi!

I have some complex queries with a lot of UNION ALL statements and an ORDER
BY statement at the end. There is a limit of 49 UNION ALL statements in
Access, so I want to avoid the UNION ALL statement in the queries.
Union All appends resultsets in SQL.
Is it possible to append the result of the single queries to the Recordset,
sort the Recordset and display the result?

It's possible to append data to Recordsets that opens a table (.AddNew). But
I open a query and not a table. So the recordset is readonly.

Do you have any ideas?

I can read the query-results in an array. But than I have the problem to
sort a multidimension array.

Thank you for suggestions!
Have a nice day,
ralf

 

[peregenem]

Is it possible to append the result of the single queries to the Recordset,
sort the Recordset and display the result?

It's possible to append data to Recordsets that opens a table (.AddNew). But
I open a query and not a table. So the recordset is readonly.

Some thoughts

You can fabricate a recordset and add the data to it manually (aircode)

rs.Fields.Append "key_col", adInteger
rs.Fields.Append "data_col", adVarChar, 255, adFldMayBeNull
rs.Open
rs.AddNew Array("key_col", "data_col"), Array(rs1!MyKeyCol,
rs1!MyDataCol)
rs.AddNew Array("key_col", "data_col"), Array(rs2!KeyCol, rs1!DataCol)
....
rs.Sort = "key_col"
rs.GetString

If you have more than 49 tables to append, it is likely your schema is
poorly designed.

 

[news.microsoft com]

Than you for your answer!

Sorry, I'm using DAO 3.6. I forgot to say. I think this is only possible in
ADO?
There is a description for ADO here:
http://www.aspheute.com/artikel/20000516.htm if anyone is interested.

Is there a possiblity in DAO also? In which way do I have to open the
Recordset?

Thanks for answers!
ralf

 

[peregenem]

Sorry, I'm using DAO 3.6. I forgot to say. I think this is only possible in
ADO?

Is there a possiblity in DAO also?

You can have ADO and DAO in the same project and you can write data
from your DAO recordsets to your ADO recordset.