Syntax error (comma) in query expression

[681212]

It would be great if someone helps me out!

I'm getting an error as "Syntax error (comma) in query expression" for the below code in MS ACCESS 2007.

CurrentDb.Execute "INSERT INTO empmast(empno,empname,scale) SELECT ([EMP NO ], [EMP NAME], [SCALE]) from TABLEA"

Thanks in advance

[Nauticalgent]

Only thing I would suggest is removing the parentheses in your select statement. But then, I avoid SQL statements and use stored queries whenever possible.

Have you considered using an Append Query and let Access do the work? They are much more efficient and you can still use the .Execute method to run them.

[NeoPa]

If it isn't the space after "[Emp No" and before the next "]" then you'll need to show us the actual SQL used (See How to Debug SQL String).

[NeoPa]

NauticalGent:
Have you considered using an Append Query and let Access do the work? They are much more efficient and you can still use the .Execute method to run them.

As the code shown is exactly that - an example of an Append Query - I'm not sure that's really a supportable comment.

That said, the extra parentheses may well be the issue. Good spot.

[Nauticalgent]

Agreed, they are one and the same. I am going on the premise that Access establishes a query plan for stored queries and does not have to take the time to convert the SQL statement.

I can imagine the overhead is negligible, but that is what I based my statement on...that and the fact I am lazy and prefer Access to do the heavy lifting for me!

[NeoPa]

I see. You're using the term "Query" for the Access object that's a QueryDef. Fair enough, but I didn't realise that (SQL strings are also generally known as Queries).

As you say, the delay would be negligible. Hard even to quantify.

[Nauticalgent]

Curious as to how the OP is getting along with sorting this out..

[NeoPa]

Often the way. Unfortunately we have no control over members' manners, or even their lives. There may be various good reasons why they haven't replied as yet. Certainly, an approach whereby you deal with what's in front of you only, is recommended. If you watch the kettle you'll only let yourself in for frustration.