ADO Recordsets

[briana02]

Hi everyone,

Is there anyway to combine, or query an ADO recordset. I have two recordset objects that are connected to separate Databases. However, I need to combine all the records in one recordset with all the non-repeating data in the other.

Example

Dim adCon1 as ADODB.Connection
Dim adCon2 as ADODB.Connection
Dim rs1 as ADODB.Recordset
Dim rs2 as ADODB.Recordset

'initialize & set all the properties for
'the connections and recordsets...blah blah blah

rs1.Open "SELECT DISTINCT(AgentID) FROM Table_A" _
, adCon1, adOpenForwardOnly, adLockOptimistic, _
adCmdText

rs2.Open "SELECT DISTINCT(AgentID) FROM Table_B" _
, adCon2, adOpenForwardOnly, adLockOptimistic, _
adCmdText

'Now I need to somehow combine all the records
'in rs1 with all the records in rs2 that are
'aren't duplicates(ie don't already exist
'in rs1). We sell under two brand
'names so we have two tables to track sales
'for each brand. However, we now need to
'generate a report of total sales per agent for
'each brand (in case you're wondering why we
'have essentially two of the same tables running
'around...but hey, I didn't design the DB...lol)

[Raptors Fan]

I also wanted to query a recordset but didn't find any way of doing it. What you could do is go through both recordsets through VB code instead of an SQL statement and save the result in new recordset.

Look at this link on an example on how to build a recordset from scratch without the need to connect to a database.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dninvb00/html/ADORecordset.asp

Good Luck,

RF

[phunkydude]

If you don't need to manipulate the two recordsets for the required functionality you can try call the recordset with the following SQL:
sSQL_A="SELECT DISTINCT(AgentID) FROM db_A_name..Table_A"
sSQL_B= "SELECT DISTINCT(AgentID) FROM db_B_name..Table_B"
rs.open sSQL_A & " UNION " & sSQL_B, adCon1, adOpenForwardOnly, adLockOptimistic, _adCmdText



HTH and hey, it's Friday!