Use multiple Inner join for SQL query in VBA

Question

I want to run the sql queries via VBA excel below are my code which is successfully run for me for two table inner join.

Now I want to edit the query in which i can combined more the 2 tables with the help of Inner join.

    Sub SQL()

        Dim cn As ADODB.Connection
        Dim rs 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"";"

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

        cn.Open strCon

        strSQL = "SELECT [Sheet2$].[Sr], [Code], [Family] FROM [Sheet3$] INNER JOIN [Sheet2$] ON [Sheet2$].[Sr]=[Sheet3$].[Sr]"

''Here i want to edit the code to combined more the 2 tables with the help of Inner join.

        rs.Open strSQL, cn

        Sheet3.Range("D1").CopyFromRecordset rs

        End Sub

for example:- I have one more table in sheet 4 in which also the Sr header is available.Please guide me for the same.

MarcinSzaleniec · Accepted Answer · 2017-10-23 14:02:49Z

1

if you want just add one join more, enough would be:

strSQL = strSQL  & " INNER JOIN [Sheet4$] ON [Sheet4$].[Sr]=[Sheet3$].[Sr]"

However, you may also want to add some fields in select clause. My advice, add sheet identifier to all fields, like: [Sheet3$].[Code], you will avoid disambiguate field names if there were used in newly joined tables.

answered Oct 23, 2017 at 14:02

MarcinSzaleniec

2,2661 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MarcinSzaleniec Over a year ago

Field is named Sr.open? maybe try replace it with Sr_open. Did it worked with outcommented SQL string?

haba713 · Accepted Answer · 2021-03-14 10:34:09Z

1

You must use parentheses with multiple joins:

"FROM (( [Sheet3$] " & _ 
"INNER JOIN [Sheet2$] ON [Sheet2$].[Sr]=[Sheet3$].[Sr] ) " & _
"INNER JOIN [Sheet4$] ON [Sheet4$].[Sr]=[Sheet3$].[Sr] )"

edited Mar 14, 2021 at 10:34

answered Mar 14, 2021 at 10:17

haba713

2,7171 gold badge28 silver badges55 bronze badges

Comments

Onur · Accepted Answer · 2022-10-22 18:42:03Z

0

i tried mutli join with sum function i get other error."join is not supported"

this is the code below

rs.Open "SELECT A.CARIID, A.CARIKOD, A.CARIUNVAN, SUM (B.BORCTUTAR), SUm
     (B.ALACAKTUTAR)*-1 FROM ((CARI AS A" & _
    " LEFT OUTER JOIN DATA AS B ON A.CARIKOD=B.BORCLUKOD)" & _
    " LEFT OUTER JOIN DATA AS B ON A.CARIKOD=B.ALACAKKOD)" & _
    " GROUP BY A.CARIID, A.CARIKOD, A.CARIUNVAN", _
    cn, adOpenKeyset, adLockPessimistic

answered Oct 22, 2022 at 18:42

Onur

112 bronze badges

1 Comment

Onur Over a year ago

i got it, here is code: rs.Open " SELECT A.CARIID, A.CARIKOD, A.CARIUNVAN, SUM(D.BORCTUTAR), SUM(E.ALACAKTUTAR),FROM (CARI AS A" & _ " LEFT OUTER JOIN DATA AS D ON A.CARIKOD=D.BORCLUKOD)" & _ " LEFT OUTER JOIN DATA AS E ON A.CARIKOD=E.ALACAKKOD" & _ " GROUP BY A.CARIID, A.CARIKOD, A.CARIUNVAN", _ cn, adOpenKeyset, adLockPessimistic

Collectives™ on Stack Overflow

Use multiple Inner join for SQL query in VBA

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related