DAO SQL on multiple sources

[TopJack]

I want to use DAO within Excel (2003) to access two other seperate Excel workbooks to merge the data. I can access the data from the two other workbooks already but not sure how to merge the data. This is a snippet of my code so far .......

Dim db1 As DAO.Database, db2 As DAO.Database
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset

Set db1 = OpenDatabase("C:\filename1.xls", False, True, "Excel 8.0;")
Set db2 = OpenDatabase("C:\filename2.xls", False, True, "Excel 8.0;")

DoEvents

Set rs1 = db1.OpenRecordset("select * from [Sheet1$]")
Set rs2 = db2.OpenRecordset("select * from [Sheet1$]")

'do some processing

rs1.Close
rs2.Close

db1.Close
db2.Close

Ideally I would run a SQL statement that would join the tables/recordsets together, eg "SELECT * FROM rs1.[Sheet1$], rs2.[Sheet1$] WHERE rs1.[Field1]=rs2.[Field2]"

Any ideas, if possible and what syntax to use would be helpful.

 

[PHV]

As you are within Access, why not simply use tables linked to the spreadsheets ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TopJack]

Sorry PH I didn't make myself clear. This is all run from Excel and not Access.

 

[PHV]

Oops, sorry :~/

 

[TopJack]

The only way I can think of doing this is to step through each recordset record by record (assuming of course they are in some kind of join order) and manually merge them.