VBA Access Error 3134 Syntax error

Question

I'm trying to fix my first INSERT INTO statement for school. Here is the string for the CurrentDb.Execute strSQL

strSQL = "INSERT INTO [Procedure] (UserName, Date, StartTime, EndTime, Semester, FacilityID, Doctor, CaseID, ServiceID, RoleName, Trainer, Comment, Updated_By, Updated_Datetime) VALUES ('username', '12/04/2018', '01:30 PM', '02:00 PM', 'Fall', '4', 'Dr. Who', 'Cataract Extraction with Stent For Glaucoma', '4', 'OS', 'Trainer', 'Comment', 'username', '4/23/2018 7:51:00 PM');"

Procedure Field Names:

UserName, Date, StartTime, EndTime, Semester, FacilityID, Doctor, CaseID, ServiceID, RoleName, Trainer, Comment, Updated_By, Updated_Datetime

Any help is really appreciated. Thank you!! -Tyler M

Mike · Accepted Answer · 2018-04-24 03:09:21Z

2

It's because you have a field in your table that is named the same as reserved word (built in function)

Date

Rename that field in your table, to something better like

DateOfProcedure

and your query will work (after editing your query string also of course)

In Access Debug window (Ctrl + G) you can run

? AccessError(3134)

and it will return

Syntax error in INSERT INTO statement.

While you are there in the debug window type

? date

There is the reserved word you are trying to use for a field name in the table, causing the SQL query to fail.

Also two other tips (optional)

If any of those ID fields are numbers, you don't have to enclose the Value in single quotes (can have no quotes, so long as the whole query string is enclosed in double quotes)

And for date/time fields, you can use # symbol instead of single quotes to enclose a date/time value

answered Apr 24, 2018 at 3:09

Mike

1366 bronze badges

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

1 Comment

Meneghini Over a year ago

Thank you for all the information! It works great now! :D

dbmitch · Accepted Answer · 2018-04-24 03:06:22Z

0

Try putting square brackets around your field names

I know for sure that [Date] is a reserved word in MS-Access - so that one should be wrapped as in

strSQL = "INSERT INTO [Procedure] (UserName, [Date],....

answered Apr 24, 2018 at 3:06

dbmitch

5,3964 gold badges27 silver badges39 bronze badges

Comments

Gustav · Accepted Answer · 2018-04-24 07:50:38Z

0

Also, date/time fields must be passed proper values, and no quotes around numeric values:

strSQL = "INSERT INTO [Procedure] (UserName, [Date], StartTime, EndTime, Semester, FacilityID, Doctor, CaseID, ServiceID, RoleName, Trainer, Comment, Updated_By, Updated_Datetime) VALUES ('username', #12/04/2018#, #01:30 PM#, #02:00 PM#, 'Fall', 4, 'Dr. Who', 'Cataract Extraction with Stent For Glaucoma', 4, 'OS', 'Trainer', 'Comment', 'username', Now());"

answered Apr 24, 2018 at 7:50

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

VBA Access Error 3134 Syntax error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related