Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

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?

Thanks

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.
0
hindersaliva
Asked:
hindersaliva
  • 2
  • 2
  • 2
  • +1
4 Solutions
 
Gustav BrockCIOCommented:
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.

/gustav
0
 
hindersalivaAuthor Commented:
Gustav,
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.
0
 
PatHartmanCommented:
As long as the date is unambiguous,  Access will "correct" the format.  But 01/05/16 will be JANUARY 5th NOT MAY 1st.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Gustav BrockCIOCommented:
First:
>  I get an error 'syntax error in INSERT statement'.
Then:
> It's happy with this though ...

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

/gustav
0
 
[ fanpages ]IT Services ConsultantCommented:
Is the column [Current] being detected as a reserved word?
0
 
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!
0
 
[ fanpages ]IT Services ConsultantCommented:
You're very welcome :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now