Append rows to a Recordset instead of using Union All statement

  • Thread starter Thread starter news.microsoft com
  • Start date Start date
N

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
 
news.microsoft com said:
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.
Click to expand...

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.
 
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
 
news.microsoft com said:
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?
Click to expand...

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