Access 2016 SQL INSERT statement via Excel VBA ADO mystery

When I send this to Access 2016 via ADO/VBA I get an error 'syntax error in INSERT statement'.

But when I copy/paste the same into the Query window in Access it runs OK. No error!

Am I missing something?


INSERT INTO tblManualData (ApplicationID, Status, StartDate, Hospital, Induction, InductionDate, DocsOS, CertNo, DBSReturnDate, GeneralNotes, AW, Current, Withdrawn, OnHold) VALUES ('6913078','On Hold','23/09/2016','UCH','Yes','01/01/2016','Birth Certificate',' 11111','20/06/2016','Some notes go here.','23/09/2016','23/09/2016','23/09/2016','23/09/2016')

Open in new window

Note: the Date fields are Date/Time. All others are Short Text. The GeneralNotes is Long Text.
Who is Participating?
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Is the column [Current] being detected as a reserved word?
Gustav BrockConnect With a Mentor CIOCommented:
You must use correct string expressions for the date values, like:

VALUES ('6913078','On Hold',#2016/09/23#,'UCH','Yes',#2016/01/01#,'Birth Certificate',  ..etc.

hindersalivaAuthor Commented:
It's happy with this though ...

INSERT INTO tblManualData (ApplicationID, Status, StartDate) VALUES ('6913078','On Hold','23/09/2016')

Open in new window

Also it's happy with the UK date format.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

PatHartmanConnect With a Mentor Commented:
As long as the date is unambiguous,  Access will "correct" the format.  But 01/05/16 will be JANUARY 5th NOT MAY 1st.
Gustav BrockConnect With a Mentor CIOCommented:
>  I get an error 'syntax error in INSERT statement'.
> It's happy with this though ...

Both can't be right. The expressions must be as stated to play safe.

hindersalivaAuthor Commented:
Got it!
Yes fanpages. I made it [Current] and it worked. Looks like Current is a reserved word.

Also, I changed the Date data to have # around it. Thanks for the advice.

Thanks all!
[ fanpages ]IT Services ConsultantCommented:
You're very welcome :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.