Syntax error in FROM clause C# and MSAccess

Question

I have a program that fetch data from database. I have two different databases (MSAccess and Oracle). I kept receiving "Syntax error in FROM clause" if I set the connection to MSAccess but it works fine with Oracle.

Here's the code:

using (IDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\CYME\DB\cyme-31R2.mdb"))
  {
    connection.Open();
    string query = $"SELECT g.NetworkID, g.ACTIVEGENERATION, g.DEVICENUMBER, g.DEVICETYPE, u.DATAVALUESTR as PremiseId " +
            $"FROM CYMDGGENERATIONMODEL g " +
            $"JOIN CYMDEVICEUDD u ON g.DEVICENUMBER = u.DEVICENUMBER " +
            $"JOIN CYMLOADMODEL m ON g.LOADMODELID = m.LOADMODELID " +
            $"WHERE  u.DATAVALUESTR IS NOT NULL AND u.DATAID='PremiseID' AND m.LOADMODELNAME='LFM' AND g.DEVICETYPE = 37 " +
            $"GROUP BY  g.NetworkID, g.ACTIVEGENERATION, g.DEVICENUMBER, g.DEVICETYPE, u.DATAVALUESTR";

    var result = connection.Query<CymDGGenerationModel>(query).ToList();

    return result;
  }

For your information, table "CYMDGGENERATIONMODEL" and "CYMDEVICEUDD" are created but have no data for Access. Is this the reason I received that error or it's something else? Thanks in advance.

Answer 1

There are 2 issues with your SQL sample which make it incompatible with Access SQL.

1. Access requires parentheses in the FROM clause for queries which include more than one join.
2. Access does not accept just JOIN as a synonym for INNER JOIN. You must specify the type (INNER, LEFT, or RIGHT) for each join.

See whether this simplified SELECT statement, which focuses on the FROM piece, eliminates that "Syntax error in FROM clause" complaint.

SELECT *
FROM
    (CYMDGGENERATIONMODEL g
    INNER JOIN CYMDEVICEUDD u ON g.DEVICENUMBER = u.DEVICENUMBER)
    INNER JOIN CYMLOADMODEL m ON g.LOADMODELID = m.LOADMODELID

If possible, test the SQL in the Access query designer as Steve recommended.