syntax error in join operation RRS feed

  • Question

  • Hi ,

    I am getting an syntax error for the following sql statement. i am trying to join data from 2 excel sheets to paste data in 3rd sheet.

    strSQL = "SELECT data.*, franchise.* FROM [franchise$] " & _
                  "INNER JOIN [data$] ON franchise.Location = data.TalukaName " & _
                  "WHERE data.TalukaName='Thane (East)'"

    i have also provided the entire code for refernece. please help. i wasted lot of time to figure out the error.

    Sub trial()
        Dim cn As ADODB.Connection
        Dim rs1 As ADODB.Recordset
        Dim rs2 As ADODB.Recordset

        strFile = ThisWorkbook.FullName
        strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

        strSQL = "SELECT DISTINCT [talukaname] FROM [data$]"

        Set cn = CreateObject("ADODB.Connection")
        Set rs1 = CreateObject("ADODB.Recordset")

            cn.Open strCon
            rs1.Open strSQL, cn

                While Not rs1.EOF
                    strSQL = "SELECT data.*, franchise.* FROM [franchise$] " & _
                             "INNER JOIN [data$] ON franchise.Location = data.TalukaName " & _
                             "WHERE data.TalukaName='Thane (East)'"

                    Set rs2 = CreateObject("ADODB.Recordset")
                        rs2.Open strSQL, cn

                        While Not rs2.EOF
                            ActiveCell.Value = rs.Fields("Name")
                            ActiveCell.Offset(0, 1).Value = rs.Fields("talukaname")
                            ActiveCell.Offset(0, 2).Value = rs.Fields("clickscount")
                            ActiveCell.Offset(0, 3).Value = rs.Fields("fbcount")
                            ActiveCell.Offset(0, 4).Value = rs.Fields("clicksamount")
                            ActiveCell.Offset(0, 5).Value = rs.Fields("fbamount")
                            ActiveCell.Offset(0, 6).Value = rs.Fields("totalmount")
                            ActiveCell.Offset(0, 7).Value = rs.Fields("email address")
                            ActiveCell.Offset(0, 8).Value = rs.Fields("location")
                            ActiveCell.Offset(1, 0).Select

                    Set rs2 = Nothing

        Set rs1 = Nothing
        Set cn = Nothing
    End Sub

    Thursday, March 26, 2015 6:50 PM

All replies

  • Hi naswiz,

    This is the error message you got, right?

    You should use alias for the worksheet or the worksheet's full name. For example, it works fine if you change the SQL string like this:

    strSQL = "SELECT d.*, f.* FROM [franchise$] as f " & _
                              "INNER JOIN [data$] as d ON f.Location = d.TalukaName " & _
                              "WHERE d.TalukaName='Thane (East)'"

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 27, 2015 9:38 AM