How to Use sql FULL OUTER JOIN query in VBA Excel

Question

I would like to use FULL OUTER JOIN sql query via VBA excel.Below is my code and tables. Please guide how can i use the same.

1st Table-

2nd Table-

VBA Code:-

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 * FROM [Sheet3$] FULL OUTER JOIN [Sheet2$] ON [Sheet2$].[Sr]=[Sheet3$].[Sr]"
rs.Open strSQL, cn

Sheet5.Range("D1").CopyFromRecordset rs

End Sub

In my above code i am getting below error.

Tim Biegeleisen · Accepted Answer · 2017-10-30 11:22:57Z

1

I'm not sure that your current error is actually even being caused by the query. But MS Access does not support FULL OUTER JOIN. However, you can simulate it using a UNION of two queries:

SELECT s1.col1, s1.col2
FROM [Sheet3$] s1
LEFT JOIN [Sheet2$] s2
    ON s1.[Sr] = s2.[Sr]
UNION ALL
SELECT s2.col1, s2.col2
FROM [Sheet2$] s1
LEFT JOIN [Sheet3$] s2
    ON s1.[Sr] = s2.[Sr]

I arbitrarily chose to select two columns from Sheet3$ called col1 and col2. You are free to select any columns you want, though I would advise you to move away from using SELECT *, as it would generally leave your code prone to breaking at some point down the road. Instead, just select the columns you want explicitly.

answered Oct 30, 2017 at 11:22

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Suraj Rao · Accepted Answer · 2022-05-17 06:30:33Z

1

As Harassed Dad said here, ADODB does not support FULL OUTER JOIN or CROSS JOIN. This might work.

SELECT * FROM [Sheet3$], [Sheet2$]

edited May 17, 2022 at 6:30

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered May 17, 2022 at 6:27

JY Park

155 bronze badges

Comments

marc_s · Accepted Answer · 2018-07-28 20:30:45Z

0

Query #1:

strSQL = "SELECT a.* ,b.*  FROM [Sheet2$] as a LEFT JOIN [Sheet3$] as b ON a.[Sr]=b.[Sr]"

Query #2:

strSQL = "SELECT a.* ,b.Family  FROM [Sheet2$] as a LEFT JOIN [Sheet3$] as b ON a.[Sr]=b.[Sr]"

edited Jul 28, 2018 at 20:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 30, 2017 at 11:36

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

2 Comments

Ralph Over a year ago

The OP specifically asks for a FULL OUTER JOIN and not a LEFT JOIN as proposed in your solution. So, I am not sure why you posted this as a solution. Could you please elaborate?

Dy.Lee Over a year ago

@Ralph, I think that in adodb, it is not supported full outer join. In sheet2 Sr have all data of sheet3 Sr field. So the result seems to be same with full outer join.

Suraj Rao · Accepted Answer · 2022-05-17 06:30:48Z

0

ADODB doesn't support FULL OUTER JOIN, but table A OUTER JOIN Table B is equivalent to TableA LEFT JOIN TableB UNION TableB Left Join Table A if you remove duplicates. So

Select DISTINCT * From (select col1 from TableA LEFT Join TableB ON TableA.sr = TableB.sr Union select col1 from TableB Left Join Table A ON TableB.sr = TableA.sr)

will mimic a full outer

edited May 17, 2022 at 6:30

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Oct 30, 2017 at 13:14

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

1 Comment

POliveira Over a year ago

Am I missing something or woudn't be sufficient to use a UNION? Like: select col1 from TableA Union select col1 from TableB

Collectives™ on Stack Overflow

How to Use sql FULL OUTER JOIN query in VBA Excel

4 Answers 4

Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related