Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    Unanswered: [Microsoft][ODBC Microsoft Access Driver] Join expression not supported

    Hi All ,

    I am trying to Execute this Query on Access 200 but I am getting an error :

    "Error Message: [Microsoft][ODBC Microsoft Access Driver] Join expression not supported.
    Error Code: -3530
    Error State: S1000"

    Anyone having any Idea why this is happening ?Here is the query :

    SELECT N0.NameID, N0.Client, N0.Advisor, I1.Lastname, I1.Firstname, I1.MidInit,I1.Prefix, I1.Suffix,I1.Nickname, .Type,I2.Status, .SourceType,
    FROM TableA N0 LEFT OUTER JOIN
    TableB I1 ON (N0.NameID = I1.NameID),TableC I2
    WHERE (N0.NameID = I2.NameID) AND N0.NameID > 0)
    ORDER BY N0.NameID


    NOTE : if i remove the third table i.e tableC and the related columns & condition it works .

    Thanks,
    Nirbhay.

  2. #2
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    You have mixed 2 different ways of coding an SQL query. The hanging TableC is causing the problem. Your query should read

    SELECT N0.NameID, N0.Client, N0.Advisor, I1.Lastname, I1.Firstname, I1.MidInit,I1.Prefix, I1.Suffix,I1.Nickname, .Type,I2.Status, .SourceType,
    FROM (TableA N0 LEFT OUTER JOIN
    TableB I1 ON (N0.NameID = I1.NameID)) INNER JOIN TableC I2 ON (N0.NameID = I2.NameID)
    WHERE N0.NameID > 0)
    ORDER BY N0.NameID
    Roger Hampson
    XI - ecs (UK) Ltd

  3. #3
    Join Date
    Sep 2002
    Posts
    2
    Hi xiecsuk,

    well ur suggestion really helped , that indeed was the problem . but this seems to be a poblem with MsAccess only.
    ex:

    select A.* , B.* , c.* from contact A left outer join
    personal B on (A.contactid = B.contactid ) left outer join address c on (A.contactid = c.contactid )
    where A.recordtype = 1

    this query will run on sybase and all other DB but it won't run on access unless u place parantheses around the first two tables in from clause. it will give you syntax error in from clause (In Access).

    let me know if i am wrong ...

    Thanking u for ur help
    Nirbhay.

Posting Permissions

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