Access VBA中能否使用FULL OUTER JOIN？是否仍需用UNION组合左右连接？

Great question! Let’s cut to the chase: when you create a Recordset in VBA, you’re still using Access’s Jet/ACE SQL engine—the same one that powers Access query objects. That means you can’t skip the UNION workaround; direct FULL OUTER JOIN syntax isn’t supported here either.

Example 1: Attempting Direct FULL OUTER JOIN (Will Fail) #

This code will throw a syntax error because Jet/ACE doesn’t recognize FULL OUTER JOIN:

Sub AttemptFullOuterJoin()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb()
    
    ' This will cause a syntax error
    strSQL = "SELECT Table1.ID, Table1.Value, Table2.OtherValue " & _
             "FROM Table1 FULL OUTER JOIN Table2 ON Table1.ID = Table2.ID"
    
    On Error Resume Next
    Set rs = db.OpenRecordset(strSQL)
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description & vbCrLf & "Direct FULL OUTER JOIN is not supported in Access SQL"
    End If
    
    Set rs = Nothing
    Set db = Nothing
End Sub

Example 2: Correct UNION Workaround (Will Work) #

To get the equivalent of a FULL OUTER JOIN, combine a LEFT JOIN and RIGHT JOIN with UNION (use UNION ALL if you want to keep duplicate rows, but UNION removes them by default):

Sub UnionFullOuterJoin()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb()
    
    ' Combine LEFT JOIN and RIGHT JOIN with UNION
    strSQL = "SELECT Table1.ID, Table1.Value, Table2.OtherValue " & _
             "FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID " & _
             "UNION " & _
             "SELECT Table2.ID, Table1.Value, Table2.OtherValue " & _
             "FROM Table1 RIGHT JOIN Table2 ON Table1.ID = Table2.ID"
    
    Set rs = db.OpenRecordset(strSQL)
    
    ' Example: Loop through the recordset to verify results
    If Not rs.EOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            Debug.Print "ID: " & rs!ID & ", Value: " & Nz(rs!Value, "N/A") & ", OtherValue: " & Nz(rs!OtherValue, "N/A")
            rs.MoveNext
        Loop
    End If
    
    Set rs = Nothing
    Set db = Nothing
End Sub

Which One Should You Use? #

Always use the second approach (UNION of LEFT/RIGHT JOINs). The first example will fail because Access’s SQL engine doesn’t support FULL OUTER JOIN natively—this limitation applies whether you’re working in query objects or VBA Recordsets.

内容的提问来源于stack exchange，提问作者Isaac Reefman