CODEWITHSUNDEEP
sqlms-accessvba
Isaac Reefman
Isaac Reefman

Reputation: 597

Can I use FULL OUTER JOIN in access VBA?

I know I can't user FULL OUTER JOINs in access query objects, and instead need to UNION a left and a right join. Can I avoid this extra business when creating recordsets within VBA, or does VBA use the same SQL system?

For example:

Recordset = CurrentDb.OpenRecordset(_
"SELECT Table1.Field1, Table2.Field2 FROM Table1" & _
"FULL OUTER JOIN Table2 On Table1.Field1 = Table2.Field1")

Or do I have to do something like this:

Recordset = CurrentDb.OpenRecordset(_
"SELECT Table1.Field1, Table2.Field2 FROM Table1" & _
"LEFT JOIN Table2 On Table1.Field1 = Table2.Field1" & _
"UNION SELECT Table1.Field1, Table2.Field2 FROM Table1" & _
"RIGHT JOIN Table2 On Table1.Field1 = Table2.Field1")

Upvotes: 0

Views: 646

Answers (1)

Zev Spitz
Zev Spitz

Reputation: 15297

It depends which database you are trying to connect to. If you are connecting to a database which supports FULL OUTER JOIN, then you could use FULL OUTER JOIN from VBA.

Since you're using CurrentDb.OpenRecordset, you are querying with the Access / Jet database engine, and FULL OUTER JOIN is not supported.

If you are targeting another RDBMS which does support FULL OUTER JOIN, then you could create a pass-through query with FULL OUTER JOIN and call OpenRecordset on that query.

Upvotes: 2

Related Questions