VBA ADODB query Excel workbook as source, how to join?

[Jaymond Flurrie]

I'm trying to query one Excel Workbook, two separate places to join them.

Here's the source book, the name of the worksheet is Sheet:

And here's the code. Note that I have a simpler query there that doesn't give me any errors, just to prove the problem is somehow with the join. The one with join gives run-time error -2147217900 (80040e14) with message "Syntax error in FROM clause".

Sub JoinTest()
    'Here's the SQL I'm trying to perform
    'SELECT o.OneValue
    'FROM One o
    'JOIN Two t
    'ON o.OneID = t.TwoID
    
    Dim connection As Object
         
    Set connection = CreateObject("ADODB.Connection")
    With connection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
        .Open
    End With
    
    
    Dim workingsql As String
    workingsql = "SELECT o.OneValue " & _
    " FROM [Sheet$A1:B4] o "
    
    Dim workingresult As Object
    Set workingresult = connection.Execute(workingsql)
    
    Dim nonworkingsql As String
    nonworkingsql = "SELECT o.OneValue " & _
    " FROM [Sheet$A1:B4] o " & _
    " JOIN [Sheet$E1:G5] t " & _
    " ON o.OneID = t.TwoID"
    
    Dim nonworkingresult As Object
    Set nonworkingresult = connection.Execute(nonworkingsql)
    
    'And then whatever handling logic here, I just want that join to work to begin with
End Sub

My Excel version is actually 16.0, but that 12.0 seems to work better (well, excluding this join) for some reason. Using 16.0 in connection properties gives "Could not find installable ISAM", but let's not concentrate on this version part unless it is really mandatory for the join to work. I just want the join to work, the rest doesn't really matter right now.

 

[nguyendang]

I'm trying to query one Excel Workbook, two separate places to join them.

Here's the source book, the name of the worksheet is Sheet:
View attachment 90703
And here's the code. Note that I have a simpler query there that doesn't give me any errors, just to prove the problem is somehow with the join. The one with join gives run-time error -2147217900 (80040e14) with message "Syntax error in FROM clause".

Sub JoinTest()
    'Here's the SQL I'm trying to perform
    'SELECT o.OneValue
    'FROM One o
    'JOIN Two t
    'ON o.OneID = t.TwoID
   
    Dim connection As Object
        
    Set connection = CreateObject("ADODB.Connection")
    With connection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
        .Open
    End With
   
   
    Dim workingsql As String
    workingsql = "SELECT o.OneValue " & _
    " FROM [Sheet$A1:B4] o "
   
    Dim workingresult As Object
    Set workingresult = connection.Execute(workingsql)
   
    Dim nonworkingsql As String
    nonworkingsql = "SELECT o.OneValue " & _
    " FROM [Sheet$A1:B4] o " & _
    " JOIN [Sheet$E1:G5] t " & _
    " ON o.OneID = t.TwoID"
   
    Dim nonworkingresult As Object
    Set nonworkingresult = connection.Execute(nonworkingsql)
   
    'And then whatever handling logic here, I just want that join to work to begin with
End Sub

My Excel version is actually 16.0, but that 12.0 seems to work better (well, excluding this join) for some reason. Using 16.0 in connection properties gives "Could not find installable ISAM", but let's not concentrate on this version part unless it is really mandatory for the join to work. I just want the join to work, the rest doesn't really matter right now.

You may try this Access SQL query and see if it works.

SELECT [Sheet1$A1:B4].[OneId]
FROM [Sheet1$A1:B4] INNER JOIN [Sheet1$E1:G5] ON [Sheet1$A1:B4].[OneId] =  [Sheet1$E1:G5].[TwoId];

 

[Jaymond Flurrie]

This seems to work:

Sub JoinTest()
    'Here's the SQL I'm trying to perform
    'SELECT o.OneValue
    'FROM One o
    'JOIN Two t
    'ON o.OneID = t.TwoID
    
    Dim conn As New ADODB.connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
    ";Extended Properties=""Excel 12.0;HDR=YES;"";"
    
    Dim query As String
    query = "SELECT o.[OneValue]" & _
    " FROM [One$A1:B4] AS o" & _
    " INNER JOIN [One$D1:F5] AS t" & _
    " ON o.[OneID] = t.[TwoID]"
    
    
    Dim rs As New ADODB.Recordset
    rs.Open query, conn
    
    conn.Close
End Sub

It is slightly different solution, but close to the original.

 

[Jaymond Flurrie]

One critical issue I had wrong is that ADODB doesn't understand JOIN. It does understands INNER JOIN.
So this doesn't work:

"SELECT o.OneValue " & _
    " FROM [Sheet$A1:B4] o " & _
    " JOIN [Sheet$E1:G5] t " & _
    " ON o.OneID = t.TwoID"

But this does:

"SELECT o.OneValue " & _
    " FROM [Sheet$A1:B4] o " & _
    " INNER JOIN [Sheet$E1:G5] t " & _
    " ON o.OneID = t.TwoID"