Using VBA-Excel for an SQL query across multiple databases

Question

Using Excel 2010, SQL and DAO

I am trying to execute a query against tables which reside outside the current spreadsheet. This would be trivial, inside Access, with linked tables/databases, but using VBA in excel, I am stymied.

Presuming these:

ExcelFileOne; Tab; various headed field/columns
ExcelFileTwo; Tab; various headed field/columns

I want to execute a query inside a third excel file, that goes something like this [using dot notation for explanation, not coding....] -- a simple example:

SELECT FileOne.[Tab$].Fields, FileTwo.[Tab$].Fields, etc.
    FROM FileOne, FileTwo, Thisworkbook
    WHERE (FileOne.[Tab$].field2 <> FileTwo.[Tab$].Field2) 
        AND (ThisWorkbook.[Tab$].Field1 ....)

Basically, I want to duplicate what Access will do natively, for that linked file.

Pointers in the right directions ?

[[ I could use a pointer towards why using "Excel 8.0..." in a connection works or fails on Excel2010, with macro files, and how to load the 12 or 14 variant in a network/system closed to users.... ]]

Research ODBC driver for EXCEL; perhaps starting here: https://www.connectionstrings.com/microsoft-excel-odbc-driver/ — Pieter Geerkens, Jul 24 '16 at 18:11
Excel is not a DB server and does not come with a native DB engine. It is merely able to understand SQL with the help of external libraries like `ADO` or `DAO`. If you have Office 2016 or 2013 and installed [PowerQuery](https://support.office.com/en-us/article/Introduction-to-Microsoft-Power-Query-for-Excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605) then this is **now** possible with this new Add-In. If not, you'll have to rely on Access or SQL-Server to do this job for you (or transfer both sheets in one file and then transform / multiply them using VBA to your needs). — Ralph, Jul 24 '16 at 19:46

score 4 · Accepted Answer · answered Jul 25 '16 at 01:05

You can indeed query other workbooks using DAO and ADO directly in a SQL statement and likewise query Access databases tables by simply referencing their paths. Conversely, within an Access query you can query Excel workbooks! This is testament to the fact that Jet/ACE SQL engine (Windows .dll files) is not restricted to any one MS Office product or Windows program but a tool for all.

In both examples below, macros make a direct connection to first workbook and in SQL query each indirectly connects to second workbook. You can run code inside or outside either workbooks. Also both runs genric INNER JOIN on FileOne and FileTwo worksheets but any compliant Jet/ACE SQL statement should work. And both output query results in a pre-existing RESULTS tab.

DAO

Dim dbE As Object, db As Object, rst As Object
Dim sqlString As String
Dim i As Integer
Const dbOpenDynaset = 2, dbReadOnly = 4

' OPEN DB CONNECTION
Set dbE = CreateObject("DAO.DBEngine.120")  'ALSO TRY: DAO.DBEngine.35 OR .36   
Set db = dbE.OpenDatabase("C:\Path\To\FileOne.xlsm", False, True, "Excel 12.0 Xml;HDR=Yes")

' OPEN QUERY RECORDSET
sqlString = " SELECT * FROM [TAB$] t1" _
              & " INNER JOIN (SELECT * FROM" _
              & " [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\FileTwo.xlsm].[TAB$]) t2" _
              & " ON t1.ID = t2.ID"

Set rst = db.OpenRecordset(sqlString, dbOpenDynaset, dbReadOnly)

' COLUMNS
For i = 1 To rst.Fields.Count
    Worksheets("RESULTS").Cells(1, i) = rst.Fields(i - 1).Name
Next i

' DATA ROWS
Worksheets("RESULTS").Range("A2").CopyFromRecordset rst

rst.Close
db.Close

Set rst = Nothing
Set db = Nothing
Set dbE = Nothing

ADO

Dim conn As Object, rst As Object, fld As Object
Dim strConnection As String, strSQL As String
Dim i As Integer

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' OPEN DB CONNECTION
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                  & "Data Source='C:\Path\To\FileOne.xlsm';" _
                  & "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"        

conn.Open strConnection

' OPEN QUERY RECORDSET
strSQL = " SELECT * FROM [TAB$] t1" _
            & " INNER JOIN (SELECT * FROM" _
            & " [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\FileTwo.xlsm].[TAB$]) t2" _
            & " ON t1.ID = t2.ID"

rst.Open strSQL, conn

' COLUMNS
For i = 1 To rst.Fields.Count
     Worksheets("RESULTS").Cells(1, i) = rst.Fields(i - 1).Name
Next i

' DATA ROWS
Worksheets("RESULTS").Range("A2").CopyFromRecordset rst

rst.Close
conn.Close

Set rst = Nothing
Set conn = Nothing

Thanks...! Other meandering thoughts: 1) I see the dbe object, is that creating the singular world into which the query to all three files [local and external] can live? 2) Why 12, and if 8 works by 12 doesn't [for lack of the 12 on my closed office machine, what to do when I can't load software, etc...] 3) I assume the 36 is a reference to the DAO 3.6 on my MS install, right? 4) Why not a DAO connection, as in tooling around the object model, I see that there, too. — John, Jul 25 '16 at 11:46
Use whatever DAO version you have installed. Or use ADO which requires no versions and translatable to other databases (MySQL, SQL Server, etc.) -just change string connections and SQL statements. And as mentioned, examples here first connect to first Excel file and inside the SQL query connects to the second. — Parfait, Jul 25 '16 at 19:00
This worked, @Parfait, but when I reached out towards a CSV file, I encounter errors placing the external reference within the FROM clause. Can you peak? — John, Aug 07 '16 at 22:26

Using VBA-Excel for an SQL query across multiple databases

1 Answers1

Linked