Joining 2 tables from 2 databases using Excel VBA (ADO)

[ossuary]

Hello,

I've been struggling with this today, and searches here and on google failed to come up with a workable solution. I'm hoping someone can help me!

I have 2 Access databases, each with 1 table in them, that I need to get into a single recordset using Excel VBA (ADO). The 2 tables have identifier fields in common, so they are joinable, but I don't know the syntax to bring them together across 2 connections into a single "container."

Database 1's table has the fields: Region, Account, Active, and Owner
Database 2's table has the fields: Region, Account, Required, and Requested

What I need to end up with is a single recordset that I can dump into Excel which contains the fields: Region, Account, Active, Owner, Required, and Requested, containing all records that are in both tables / databases.

I would love to just combine the data in these databases into a single database, but unfortunately I am not their owner so can't muck around with them... I just need to pull the data into Excel.

Can someone please give me a hand with this?

 

[alansidman]

This is a possible solution: Open a new database and import or link each of the tables to the new database. Once you have the data in the new data base, create a query and join the two tables on the common field. Run the query and export to an excel spreadsheet.

Alan

 

[ossuary]

Hi,

Thanks for the response, but I need to be able to call this from Excel on demand... this is not a one-off. I will be using the information that this VBA query pulls on the fly to perform other actions, and it will be called by users who don't understand how any of the code or databases work.

 

[boblarson]

I think Alan is on to about the only solution possible. You create an Access database and put it where the Excel workbook can connect with it for anyone. You link the tables in Access so that they both are within that database. You can then connect to THAT Access database and the tables within should be available to your Excel workbook project as if they were local tables. So you don't need to build a query or anything inside of Access, you are just using it to link both the tables to one source where you can query them.