How to use SQL Joins in Excel

Question

I saw a question updated about a year ago that took me a long way towards the solution that I need but led to more questions than answers.

Let me start by saying that I'm not a "newbie" to SQL (DB2, SQLServer, Oracle) but I'm a relative "newbie" to using SQL in Excel. In addition, I'm working in 2010 but must retain compatibilty with 2003.

What I'm trying to do is match the data in one sheet to another and report exceptions. I want to pick up 6 columns from 'Open Lines' and match them to 3 columns from 'Back Orders' and only show the exceptions. 'Open Lines' can be 1..n and 'Back Orders' can be 0..n. I only want to grab the lines where 'Back Orders' exist.

To write normal SQL, I would do something like this:

SELECT 
O.[Part Number], O.[Part Desc], O.[Source Domain], O.[Ship Qty], O.[Date Created],
B.[Dest Domain], B.[Quantity], B.[Date Created] 
FROM [OPEN LINES] O
JOIN [Back Orders] B ON O.[Part Number] = B.[Part Number]
WHERE B.[Part Number] IS NOT NULL
ORDER BY O.[Part Number] 

The problem is that I can't get past the join without an error. This is what I have so far.

rs.Open "SELECT [OPEN LINES$].[Part Number], [OPEN LINES$].[Part Desc], " $ _
"[OPEN LINES$]. [Source Domain], " & _
"[OPEN LINES$].[Ship Qty], [OPEN LINES$].[Date Created] " & _
"FROM [OPEN LINES$] " & _
"JOIN [Back Orders$] ON [OPEN LINES$].[Part Number] = [Back Orders$].[Part Number] " & _
"ORDER BY [OPEN LINES$].[Part Number] ;", cn

I would appreciate any help that you can provide.

Gord

Solution

Try this:

rs.Open "SELECT [OPEN LINES$].[Part Number], [OPEN LINES$].[Part Desc], " $ _
    " [OPEN LINES$]. [Source Domain], " & _
    " [OPEN LINES$].[Ship Qty], [OPEN LINES$].[Date Created] " & _
    " FROM [OPEN LINES$], [Back Orders$] " & _
    " WHERE [OPEN LINES$].[Part Number] = [Back Orders$].[Part Number] " & _
    " ORDER BY [OPEN LINES$].[Part Number]", cn

You don't need the ending semicolon.

Here's a simpler example (tested in XL2010)

Sub SqlJoin()

    Dim oConn As New ADODB.Connection
    Dim oRS As New ADODB.Recordset
    Dim sPath
    Dim sSQL As String

    sSQL = "select a.blah from <t1> a, <t2> b where a.blah = b.blah"

    sSQL = Replace(sSQL, "<t1>", Rangename(Sheet1.Range("A1:A5")))
    sSQL = Replace(sSQL, "<t2>", Rangename(Sheet1.Range("C1:C3")))

    If ActiveWorkbook.Path <> "" Then
      sPath = ActiveWorkbook.FullName
    Else
      MsgBox "Workbook being queried must be saved first..."
      Exit Sub
    End If

    oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sPath & "';" & _
                 "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

    oRS.Open sSQL, oConn

    If Not oRS.EOF Then
        Sheet1.Range("E1").CopyFromRecordset oRS
    Else
        MsgBox "No records found"
    End If

    oRS.Close
    oConn.Close

End Sub

Function Rangename(r As Range) As String
    Rangename = "[" & r.Parent.Name & "$" & _
                r.Address(False, False) & "]"
End Function

Comments

[Gord Royle]

Thanks Tim - Your first solution worked like a charm. I now can go on to the next steps.