"Syntax error in FROM clause" using OleDb in C# but no error for same query in Access itself

Question

When using the following SQL-statement within c#/OleDbCommand.ExecuteReader I get a syntax error in FROM-clause. Using exactly the same statement in MS Access directly works fine.

SELECT 
s.idShots, s.shotdata, c.[original], s.[hash], comp.idCompetitions, comp.competitionsname, sh.idShooters, sh.firstname, sh.lastname 
FROM (([Shots] s 
INNER JOIN [ShotsCertificate] c ON c.[uuid] = s.[uuid]) 
INNER JOIN [Competitions] comp ON comp.idCompetitions = s.fidCompetitions) 
INNER JOIN [Shooters] sh ON sh.idShooters = s.fidShooters ORDER BY s.idShots ASC

Within c#:

        OleDbCommand cmd2 = new OleDbCommand("", dbc);
        cmd2.CommandText = "SELECT s.idShots, s.shotdata, c.[original], s.[hash], comp.idCompetitions, comp.competitionsname, sh.idShooters, sh.firstname, sh.lastname FROM" +
            " (([Shots] s" +
            " INNER JOIN [ShotsCertificate] c ON c.[uuid] = s.[uuid])" +
            " INNER JOIN [Competitions] comp ON comp.idCompetitions = s.fidCompetitions)" +
            " INNER JOIN [Shooters] sh ON sh.idShooters = s.fidShooters" +
            " ORDER BY s.idShots ASC";

        log.Debug(cmd2.CommandText);
        OleDbDataReader r = cmd2.ExecuteReader();

The dbc connections works fine, it's used in some previous commands and everything works.

Thanks for your suggestions!

Are [Shots], [ShotsCertificate], [Competitions], and [Shooters] all tables, or is one or more of them a saved select query in Access? — Gord Thompson, Feb 16 '17 at 12:21
@Gustav : tried with "AS" after each table, still same behaviour: works in ms access, throws an exception with executeReader — Christopher, Feb 16 '17 at 13:11

score 0 · Answer 1 · answered Feb 16 '17 at 13:57

I got it working ... no comment :/

SELECT
 [Shots].[idShots], [Shots].[shotdata], [ShotsCertificate].[original], [Shots].[hash], [Competitions].[idCompetitions], [Competitions].[competitionsname], [Shooters].[idShooters], [Shooters].[firstname], [Shooters].[lastname] 
FROM (([Shots] 
   INNER JOIN [ShotsCertificate] ON [ShotsCertificate].[uuid] = [Shots].[uuid]) 
   INNER JOIN [Competitions] ON [Competitions].[idCompetitions] = [Shots].[fidCompetitions]) 
   INNER JOIN [Shooters] ON [Shooters].[idShooters] = [Shots].[fidShooters]
ORDER BY [Shots].[idShots] ASC

score 0 · Accepted Answer · answered Feb 16 '17 at 15:42

For the record, the issue was that COMP is included in the list of Access SQL Reserved Words, presumably as an abbreviation of COMPRESSION for Access DDL. Changing the table alias from comp to cmpt allowed the query to run successfully under System.Data.OleDb:

sql = "SELECT s.idShots, s.shotdata, c.[original], s.[hash], cmpt.idCompetitions, cmpt.competitionsname, sh.idShooters, sh.firstname, sh.lastname FROM" +
    " (([Shots] s" +
    " INNER JOIN [ShotsCertificate] c ON c.[uuid] = s.[uuid])" +
    " INNER JOIN [Competitions] cmpt ON cmpt.idCompetitions = s.fidCompetitions)" +
    " INNER JOIN [Shooters] sh ON sh.idShooters = s.fidShooters" +
    " ORDER BY s.idShots ASC";

Thank you! Is there any way to get more precise error messages? — Christopher, Feb 17 '17 at 07:49

"Syntax error in FROM clause" using OleDb in C# but no error for same query in Access itself

2 Answers2