how to get a recorset with data coming from two differents tables

[franky]

i wanted to have a recordset with data coming from
2 different tables which don't have any link
i just want to append the recirds of the second behind the first
in my recordset. the command union all doesn't interest me because table
have to get the same structure

[Brian]

If the tables have the same structure, why can't you use union?

This works:
select * from tblOne union select * from tblTwo

Brian

"franky" <xavierndour@post.club-internet.fr> wrote in message
news:399d4d5c$1@news.devx.com...
>
> i wanted to have a recordset with data coming from
> 2 different tables which don't have any link
> i just want to append the recirds of the second behind the first
> in my recordset. the command union all doesn't interest me because table
> have to get the same structure

[percy]

"franky" <xavierndour@post.club-internet.fr> wrote:
>
>i wanted to have a recordset with data coming from
>2 different tables which don't have any link
>i just want to append the recirds of the second behind the first
>in my recordset. the command union all doesn't interest me because table
>have to get the same structure

You can create a statement that will select the data from the two tables
you need. This will create a recordset with two sets of results. From there,
you can use the NextRecordset method of the Recordset object to reference
the recordsets. Below is a possible code snippet

SQL Statement
Dim commandText
set commandText = "select * from table1, select * from table2"

ASP Reference to Recordset
Dim rsMultiple, rsFirstRecordset, rsSecondRecordset
Set rsMultiple = Server.CreateObject("ADODB.Recordset")
rsMultiple.Open commandText

rsFirstRecordset = rsMultiple
'whatever commands you want for this recordset

rsSecondRecordset = rsMultiple.NextRecordset
'whatever commands you want for this recordset

End Code Section

rsFirstRecordset and rsSecondrecordset are really not necessary, just make
things a little easier to manipulate, you could use rsMultiple and just reference
the first recordset as rsMultiple and all subsequent recordsets as rsMultiple.NextRecordset.
The code above is very rough-probably bad SQL syntax, but hopefully you
get the drift, and assumes you already have a database connection open.
The only drawback to the next recordset method is that each recordset will
be read in order. That is why you may want to separate each recordset and
assign it to a variable, so that you can use them on different parts of the
page. Hope this helps