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.... ]]

Answer 1

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