SQL Query in Excel

[tiredofit]

I am trying to replicate this SQL query:

SELECT Table1.Fruit, Table1.Store, Table2.Store
FROM Table1 INNER JOIN Table2 ON Table1.Fruit = Table2.Fruit;

In Sheet1, I have data in the range A1 to B6, Sheet2's data ranges from A1 to B4.

This is my code:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Dim rsField As ADODB.Field
    
    Dim strcon As String
    Dim strSQL As String
        
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    rs.CursorLocation = adUseClient
    
    strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & ThisWorkbook.FullName & ";" & _
             "Extended Properties=""Excel 12.0 Macro;" & _
             "HDR=Yes;" & _
             "IMEX=1;" & _
             "MaxScanRows=0"";"
             
    cn.Open ConnectionString:=strcon
    
    Dim Address As String
    
    Address = Sheet1.Cells(1, 1).CurrentRegion.Address(0, 0)
    
    Dim Address2 As String
    
    Address2 = Sheet2.Cells(1, 1).CurrentRegion.Address(0, 0)
   
    strSQL = "SELECT [Sheet1$].[Fruit], [Sheet1$].[Store], [Sheet2$].[Store] " & _
             "FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Fruit] = [Sheet2$].[Fruit]"
            
    rs.Open Source:=strSQL, _
            ActiveConnection:=cn

but it fails on this line:

 rs.Open Source:=strSQL, _
            ActiveConnection:=cn

with an error message of:

no value given for one or more parameters.

Inspecting strSQL in the Immediate Window shows:

SELECT [Sheet1$].[Fruit], [Sheet1$].[Store], [Sheet2$].[Store] FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Fruit] = [Sheet2$].[Fruit]

Can someone please explain what is the problem?

Thanks

 

[tiredofit]

I am trying to replicate this SQL query:

SELECT Table1.Fruit, Table1.Store, Table2.Store
FROM Table1 INNER JOIN Table2 ON Table1.Fruit = Table2.Fruit;

In Sheet1, I have data in the range A1 to B6, Sheet2's data ranges from A1 to B4.

This is my code:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
   
    Dim rsField As ADODB.Field
   
    Dim strcon As String
    Dim strSQL As String
       
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    rs.CursorLocation = adUseClient
   
    strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & ThisWorkbook.FullName & ";" & _
             "Extended Properties=""Excel 12.0 Macro;" & _
             "HDR=Yes;" & _
             "IMEX=1;" & _
             "MaxScanRows=0"";"
            
    cn.Open ConnectionString:=strcon
   
    Dim Address As String
   
    Address = Sheet1.Cells(1, 1).CurrentRegion.Address(0, 0)
   
    Dim Address2 As String
   
    Address2 = Sheet2.Cells(1, 1).CurrentRegion.Address(0, 0)
  
    strSQL = "SELECT [Sheet1$].[Fruit], [Sheet1$].[Store], [Sheet2$].[Store] " & _
             "FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Fruit] = [Sheet2$].[Fruit]"
           
    rs.Open Source:=strSQL, _
            ActiveConnection:=cn

but it fails on this line:

rs.Open Source:=strSQL, _
            ActiveConnection:=cn

with an error message of:

no value given for one or more parameters.

Inspecting strSQL in the Immediate Window shows:

SELECT [Sheet1$].[Fruit], [Sheet1$].[Store], [Sheet2$].[Store] FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Fruit] = [Sheet2$].[Fruit]

Can someone please explain what is the problem?

Thanks

I've sorted it, my mistake with the field names.