combine recordsets 1

[johnaregan]

Hi all
I have 2 recordsets from 2 separate databases, the tables I am accessing are of users. I want to be able to create 2 recordsets (say rsA, rsB) for each database. I know already how to do this but then I want to create a third recordset ( rsC ) which can then be used to combine the results of the first 2.
Does anyone have any ideas of how to extract data from rsA, rsB and put it into rsC?

Many thks

 

[shaddow]

Try to use
UNION - combine only the diffrent recordsets
or
UNION ALL - combine all recordsets

SELECT * FROM Table1 WHERE put your conditions here
UNION ALL
SELECT * FROM Table2 WHERE put your conditions here

 

[johnaregan]

hi
Sorry I forgot to mention that these recordsets are actually ADO recordsets created from an ASP script.

 

[shaddow]

I know that
How u generate the recordsets?

 

[johnaregan]

As in
Set recordset = dbConnection.Execute(SQLStatement)
So the result I have is two recordsets rsA and rsB, I want create a third recordset rsC from rsA and rsB

 

[vasah20]

john -
if you separate your SQL statements with a ;, you can use the .NextRecordset method to get your next recordset.

ex:
sql = "Select * from tblA;Select * from tblB"
set rs = cn.execute(sql)
'do code to process tblA

set rs = rs.NextRecordset
'do code to process tblB

rs.Close
set rs = nothing

see:

http://www.devguru.com/Technologies/ado/quickref/recordset_nextrecordset.html

for more information on this method.

hope this helps
leo

 

[johnaregan]

leo,
thanks i think that on the right track but the thing is that tblA and tblB are on two separate databases, so in order two access and combine the two results wouldnot i need 2 recordsets and then 1 more to combine them?

 

[vasah20]

Using just ADO, yeah you would need to open up two connections to the two databases.

If you have access to your database, you can always link them together (even in MS Access), which then allows you to just make one connection, but gives you access to both DBs

leo

 

[vasah20]

Using just ADO, yeah you would need to open up two connections to the two databases.

If you have access to your database, you can always link them together (even in MS Access), which then allows you to just make one connection, but gives you access to both DBs

If you can't do this, though, then you need to loop through both recordsets, and add them to a third, custom recordset.

what I would do is store the results of RSA into ArrA, RSB into ArrayB, then loop through both of those and store the results into RSC

ex:
'assumes rsa and rsb have been created, adovbs.inc included
set rsc = server.createobject("adodb.recordset&quot
rsc.fields.append "field1", adInteger
rsc.fields.append "field2", adVarChar, 20

for i = 0 to ubound(rsaArr,2)
rsc.addnew
rsc("field1&quot = rsaArr(0,i)
rsc("field2&quot = rsaArr(1,i)
rsc.update
next

by doing it this way, you could make this into a little function, with the array and the custom recordset (byref) as parameters

hope this helps
leo

 

[johnaregan]

thanks leo, this looks like what i need