Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Unanswered: MS Access - More than 2 LEFT JOINs

    Hello,

    I am using ASP (VBScript) with an MS Access database. I understand Access expects parenthesis for SQL queries that contain multiple OUTER JOINs. For example, with 2 joins, this works:

    Code:
    objCommand.CommandText = 
    "SELECT field1_name, field2_name 
    FROM (Table1 
    LEFT OUTER JOIN Table2 ON Table2.field1_id = Table1.field1_id) 
    LEFT OUTER JOIN Table3 ON Table3.field2_id = Table1.field2_id;"

    However, when I try to extend this to 3 joins, this doesn't work:
    Code:
    objCommand.CommandText = 
    "SELECT field1_name, field2_name, field3_name 
    FROM ((Table1 
    LEFT OUTER JOIN Table2 ON Table2.field1_id = Table1.field1_id) 
    LEFT OUTER JOIN Table3 ON Table3.field2_id = Table1.field2_id) 
    LEFT OUTER JOIN Table4 ON Table4.field3_id = Table1.field3_id;"

    The error message I get is the following:
    Code:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'   
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.
    Does anyone have any idea what could be wrong with that syntax? Thanks.

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Have you tried creating the sql string in access and then pasting it back in to asp?
    Regards
    JD

    Software-Matters

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •