CODEWITHSUNDEEP
excelvba
Flekxi
Flekxi

Reputation: 3

Multiple INNER JOINS in VBA from ACCESS database

I used Access query designer to set up the joins but it didn't work in VBA.
I get Syntax Error in FROM '-2147217900 (80040e14)'

Set Signal = cnn.Execute("SELECT Table2.W, Table5.Attribute " _
    & " FROM (((((Table7 " _
    & " INNER JOIN Table5 )" _
    & " INNER JOIN Table6) " _
    & " INNER JOIN Table1) " _
    & " INNER JOIN Table2 " _
    & " ON Table1.DB_ID = Table2.Attrib_Def) " _
    & " INNER JOIN Table4 " _
    & " ON Table2.Opt_Obj_ID = Table4.Signal " _
    & " AND Table2.Object_ID = Table4.Node " _
    & " ON Table6.DB_ID = Table4.Signal) " _
    & " INNER JOIN Table3 " _
    & " ON Table4.Node = Table3.Node " _
    & " ON Table5.DB_ID = Table3.Node" _
    & " ON Table7.DB_ID = Table3.Message " _
    & " WHERE (((Table1.Name)='TypeA') " _
    & " AND ((Table7.Name)='XX') " _
    & " AND ((Table6.Name)='X')) ")

Upvotes: 0

Views: 1492

Answers (1)

JNevill
JNevill

Reputation: 50308

Your best bet to troubleshoot things like this is to send the string that is generated to your debug/immediate window:

Sub test()
    Sql = "SELECT Table2.W, Table5.Attribute " _
    & " FROM (((((Table7 " _
    & " INNER JOIN Table5 )" _
    & " INNER JOIN Table6) " _
    & " INNER JOIN Table1) " _
    & " INNER JOIN Table2 " _
    & " ON Table1.DB_ID = Table2.Attrib_Def) " _
    & " INNER JOIN Table4 " _
    & " ON Table2.Opt_Obj_ID = Table4.Signal " _
    & " AND Table2.Object_ID = Table4.Node " _
    & " ON Table6.DB_ID = Table4.Signal) " _
    & " INNER JOIN Table3 " _
    & " ON Table4.Node = Table3.Node " _
    & " ON Table5.DB_ID = Table3.Node" _
    & " ON Table7.DB_ID = Table3.Message " _
    & " WHERE (((Table1.Name)='TypeA') " _
    & " AND ((Table7.Name)='XX') " _
    & " AND ((Table6.Name)='X')) "

    Debug.Print Sql
End Sub

Copy out the sql produced when you run this and head over to a text editor so you can properly indent this mess:

SELECT Table2.W, Table5.Attribute  
FROM 
    (
        (
            (
                (
                    (
                        Table7  INNER JOIN Table5 
                    ) 
                    INNER JOIN Table6
                )  
                INNER JOIN Table1
            )  
            INNER JOIN Table2  
            ON Table1.DB_ID = Table2.Attrib_Def
        )  
        INNER JOIN Table4  
        ON Table2.Opt_Obj_ID = Table4.Signal  
            AND Table2.Object_ID = Table4.Node  
        ON Table6.DB_ID = Table4.Signal
    )  
    INNER JOIN Table3  
    ON Table4.Node = Table3.Node  
    ON Table5.DB_ID = Table3.Node 
    ON Table7.DB_ID = Table3.Message  
WHERE 
    (
        (
            (
                Table1.Name
            ) ='TypeA'
        )  
        AND 
        (
            (
                Table7.Name
            )='XX'
        )  
        AND 
        (
            (
                Table6.Name
            )='X'
        )
    )

The thing with Access's GUI for producing SQL is that it gets stupid about the parentheses. In this case, not a single parentheses is needed. I don't see anything super off here, but its ugly and difficult to diagnose what's happening. Instead use:

SELECT Table2.W, Table5.Attribute  
FROM Table7  
    INNER JOIN Table3
        ON Table3.Message = Table3.DB_ID  
    INNER JOIN Table5
        ON Table5.DB_ID = Table3.Node
    INNER JOIN Table4
        ON Table4.Node = Table3.Node 
    INNER JOIN Table6 
        ON Table6.DB_ID = Table4.Signal
    INNER JOIN Table2
        ON Table2.Opt_Obj_ID = Table4.Signal  
    INNER JOIN Table1
        ON Table1.DB_ID = Table2.Attrib_Def
WHERE
    Table1.Name ='TypeA' AND
    Table7.Name = 'XX' AND
    Table6.Name = 'X';

This is much cleaner and easier to follow. The reason we were able to remove all those parantheses is because it's only INNER JOIN and the WHERE statement is all 'ANDs'. If there were some OR or LEFT OUTER JOIN hanging out in there then we'd have to be more judicial, but I can guarantee you that Access will stick way way way way too many parentheses in there. It just loves em.

Anyway putting this back into VBA you can use:

Sub test()
    ...all your existing code...

    Dim sql As String

    sql = "SELECT Table2.W, Table5.Attribute"
    sql = sql & " FROM Table7"
    sql = sql & " INNER JOIN Table3"
    sql = sql & "     ON Table3.Message = Table3.DB_ID"
    sql = sql & " INNER JOIN Table5"
    sql = sql & "     ON Table5.DB_ID = Table3.Node"
    sql = sql & " INNER JOIN Table4"
    sql = sql & "     ON Table4.Node = Table3.Node"
    sql = sql & " INNER JOIN Table6"
    sql = sql & "     ON Table6.DB_ID = Table4.Signal"
    sql = sql & " INNER JOIN Table2"
    sql = sql & "     ON Table2.Opt_Obj_ID = Table4.Signal"
    sql = sql & " INNER JOIN Table1"
    sql = sql & "     ON Table1.DB_ID = Table2.Attrib_Def"
    sql = sql & " WHERE"
    sql = sql & " Table1.Name ='TypeA' AND"
    sql = sql & " Table7.Name = 'XX' AND"
    sql = sql & " Table6.Name = 'X';"

    Set Signal = cnn.Execute(sql)
End Sub

In the end, there is no guarantee that removing the parentheticals will help, but it will definitely remove some of the ambiguity and help you diagnose why it's not executing properly.

Upvotes: 2

Related Questions