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.

maybe "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\CYME\DB\cyme-31R2.mdb" is not correct. Should escape it: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\CYME\\DB\\cyme-31R2.mdb" — urlreader
– urlreader, Commented Mar 27, 2024 at 21:04
@urlreader sure but that would crash the connection.Open not raise a syntax error — Steve
– Steve, Commented Mar 27, 2024 at 21:54
Usually the best way to resolve these Access mysteries is to copy the query inside the Access user interface and execute it. If there is some weird Access requirement it should tell you. If everything works then copy the patched sql string from the Access interface into your program — Steve
– Steve, Commented Mar 27, 2024 at 22:05

HansUp · Accepted Answer · 2024-03-28 01:04:33Z

5

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

Access requires parentheses in the FROM clause for queries which include more than one join.
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.

answered Mar 28, 2024 at 1:04

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error in FROM clause C# and MSAccess

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related