Skip to content
Advertisement

How do I create a recordset of nested queries from different databases

The following 2 queries are taken from tables on different databases

I’d like to nest these in the following query

However, I am getting an error:
The connection cannot be used to perform this operation On the following line: rrst.Open mkQry

I guess it has to do with MyQuery and MyQuery2, both being from a different database. Is there a way to make this work?

Advertisement

Answer

Learned something new – Excel CAN pull data from multiple Access files via one SQL statement. You were on the right path with nesting. Have to set a connection, which can be the workbook or one of the Access files, then other data sources must be nested with embedded filepath. Examples:

  1. connection to workbook using ADODB objects with early binding so would need reference to Microsoft ActiveX Data Objects x.x Library.
  1. connection to one Access file
  1. DAO with early binding so reference Microsoft DAO 3.6 Object Library

I did a quick test with PowerQuery add-in and it was able to pull from both Access files and save dataset to worksheet. This does allow for a ‘live’ link to both data sources as a merged dataset.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement