Can You Query two Recordsets Using VB?

[Steve Thomas]

I posted an earlier question about joining two seperate tables but I think an easier way to ask the question is, Can I create to recordsets and then query them in VB?

Example:

Dim DB as database
Dim db2 as database
Dim Rc as recordset
Dim Rc2 as recordset

Set DB = opendatabase(C:\DATA1.mdb)
Set db2 = opendatabase(C:\DATA2.mdb)

set rc = db.openrecordset(SQLStatement)
set rc2 = db2.openrecordset(SQLStatement)

Now can I join, then at this point??????

[Bigley]

You can indeed, provided that they aremade up of exactly the same fields.
The simplest way would be to append one recordset on to the end of the other. Use the addnew method of the recordset onto which you wish to add and then set the individual fields to the value of the fields in the first record of the second recordset and loop on down through the second recordset until .EOF = true

[Steve Thomas]

I don't want to append the records together. I want to join them so that I can search. I have one database that has a customers Social Security number and some information. I have another table with the Customers Social Security number and other information. I need to search based on Socail Security number and only find those records that match. I don't want to merge them together but rather search.

[Gen-X]

What exactly do you mean when you say "JOIN" them?

Are you talking about a SQL join?

ie:

SELECT x,y,z FROM table1 t1 LEFT JOIN table2 t2 ON t1.field = t2.field

If you are talking about this then NO... you cannot join 2 seperate tables from 2 seperate databases. The reason for this is that the OpenRecordset statement gives you a "pointer" to each record on the database it comes from and interrogating data basically asks the database to retrieve the record at the time you need it. Its no longer SQL so you cannot use the JOIN command because that only works when both databases are available at the same place (ie 2 Databases on the SAME SQL Server)

What you would have to do is search through the FIRST recordset and then search through the SECOND recordset.

OR

You could base One query on another ie :

Dim DB as database 
Dim db2 as database 
Dim Rc as recordset 
Dim Rc2 as recordset 

Set DB = opendatabase(C:\DATA1.mdb) 
Set db2 = opendatabase(C:\DATA2.mdb) 

Set rc = db.openrecordset("SELECT * FROM CustSocSec") 

Do While Not rc.EOF
  Set rc2 = db2.openrecordset("SELECT * FROM AnotherCust WHERE CSSNum = " & rc("CustNum"))

  < Do something specifically with rc>

  Do While Not rc2.EOF
    < Perform whatever you like, accumulating rc2 etc >
    rc2.MoveNext
  Loop
  rc2.Close
  Set rc2 = Nothing

  < Do something with rc & accumulated rc2 >

  rc.MoveNext
Loop
rc.Close
Set rc2 = Nothing