Syntax error in FROM clause. in Inserting records from ms access into another ms access database

Question

This is my code

string stR = "INSERT INTO CHECKINOUT (USERID,CHECKTIME,CHECKTYPE,VERIFYCODE,SENSORID,WorkCode) " +
                            " SELECT a.USERID,c.CHECKTIME,c.CHECKTYPE,c.VERIFYCODE,c.SENSORID,c.WorkCode " +
                            " FROM USERINFO a " +
                            " JOIN [MS Access;DATABASE=" + open.FileName + "].USERINFO b " +
                            " ON a.BadgeNumber=b.Badgenumber " +
                            " JOIN [MS Access;DATABASE=" + open.FileName + "].CHECKINOUT c " +
                            " ON  b.USERID=c.USERID " +
                            " WHERE NOT EXISTS " +
                            " (SELECT a.USERID,c.CHECKTIME,c.CHECKTYPE,c.VERIFYCODE,c.SENSORID,c.WorkCode " +
                            " FROM USERINFO a " +
                            " JOIN [MS Access;DATABASE=" + open.FileName + "].USERINFO b " +
                            " ON a.BadgeNumber=b.Badgenumber " +
                            " JOIN [MS Access;DATABASE=" + open.FileName + "].CHECKINOUT c " +
                            " ON  b.USERID=c.USERID)";

I got exception error "Syntax error in FROM clause"

Note: I already exported all ms access tables to ms sql 2000 database and this script is working fine with no error, but with ms access I receive this kind of error, This is the first time I'm using ms access as back end, this is an old program in my office that I need to fix error.

I guess the join syntax is slightly different in access as compared with SQL Server. It need "Inner" and "Outer" keywords for joining. You may check this by creating a SQL in Access Query designer and check how its syntax generating these queries — ak1
– ak1, Commented Mar 27, 2014 at 6:45
possible duplicate of Access query error ("syntax error in from clause") — Yawar
– Yawar, Commented Mar 30, 2014 at 16:18

dspitzle · Accepted Answer · 2014-03-27 13:23:56Z

0

My first question is which FROM clause is failing. I would test each portion separately. Does the query work if you remove the WHERE NOT EXISTS clause? If so then the problem is in the subquery WHERE NOT EXISTS is referencing.

answered Mar 27, 2014 at 13:23

dspitzle

7692 gold badges10 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Syntax error in FROM clause. in Inserting records from ms access into another ms access database

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