Recordsets

[ZMAN2]

What is the syntax to join 2 recordsets? Listed below are the recordsets that I am working with and now I would like to join them. Any insight would be appreciated. FYI, I did do a search on joining recordsets in VB, but I didn't come accross anything......Thanks again.

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

DoCmd.SetWarnings True

Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM temptbl")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM temptbl2")

Set rs1 = Nothing
Set rs2 = Nothing

DoCmd.SetWarnings True

 

[FoFa]

If both source tables are the same then a simple UNION should work
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM temptbl UNION ALL SELECT * FROM temptbl2")

If they are not in the same format, then you wiould have to specify each field in the same order you need. UNION requires each select to have the same format.

 

[ZMAN2]

Thank you for your response. I may not be asking the right question. Can you join 2 recordsets with sql in vb? In other words, join rs1 and rs2 to form rs3???

 

[FoFa]

Humm, the only way I would know whould be to read each one and create a third one, unless someone else has an idea?

 

[Summerwind]

Thank you for your response. I may not be asking the right question. Can you join 2 recordsets with sql in vb? In other words, join rs1 and rs2 to form rs3???

Try something like this:

strComm = "UPDATE (tblStateSourceLoc l INNER JOIN " & _
"tblDentists d ON d.DentNo = l.DentNo) INNER JOIN " & _
"tblSurgeries s ON s.SurgeryNo = d.SurgeryNo SET " & _
"l.CoName = " & _s.Company,l.Street = s.Address," & _
"l.Town =s.Town,l.County = s.County,l.PostCode = s.Postcode," & _
"l.DentName = d.DentName"

In other words, nested inner joins I suppose. You just need to make sure that the second join (Outside the brakets) has a corresponding field in the two tables in the tables of the first join.