WHERE IN Query with two recordsets in Access VBA

Question

Hi All,

My first post here, so i hope this is the right area. I am currently trying to compare 2 recordsets, one of which has come from an Excel named range, and the other from a table in the Access database. The code for each is:

Set existingUserIDs = db.OpenRecordset("SELECT Username FROM UserData")
Set IDsToImport = exceldb.OpenRecordset("SELECT Username FROM Named_Range")

The problem is that I would like to somehow compare these two recordsets, without looping (there is a very large number of records). Is there any way to do a join or similar on these recordsets?

I can not do a join before creating the recordsets, due to the fact that one is coming from Excel, and the other from Access, so they are two different DAO databases.

The end goal is that I will choose only the usernames that do not already exist in the access table to be imported (so in an SQL query, it would be a NOT IN(table)).

Thanks for any assistance you can lend!

Regards, Bricky.

Answer 1

You can compare data from two different sources in one query. You can either use the IN keyword in SQL or, more simply, link to the Excel table (sheet) in Access and build your query around that.

EDIT an example using IN

SELECT * FROM  CompareTable 
LEFT JOIN
   (SELECT * FROM [Sheet1$]  
    IN ''[Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\Full\Path\AndName.xls]) C
ON CompareTable.CourseKey=c.CourseKey
WHERE c.CourseKey Is Null

Sheet1$ can also be Named_Range.