By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,568 Members | 1,713 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,568 IT Pros & Developers. It's quick & easy.

Access/VBA Question

100+
P: 102
Front End: Access 2010; Back End SQL Server 2008

The following produces this error messages and I do not know why: Runtime error '3134' Syntax error in INSERT INTO statement.

Expand|Select|Wrap|Line Numbers
  1. zSQL2 = _
  2.     "INSERT INTO [dbo_Notes] " & _
  3.         "(partshortages_ID, date, [by], [note])" & _
  4.         " VALUES(" & _
  5.         "  " & gblDBResult2 & _
  6.         ", '" & RDateAdded & "'" & _
  7.         ", '" & Rbuyer & "'" & _
  8.         ", '" & Me.Notes.Value & "')"
  9.  Debug.Print zSQL2
  10. DoCmd.SetWarnings False
  11. DoCmd.RunSQL zSQL2
The database definitions are:
partshortages_ID int
Date varchar(12)
By varchar(50)
note text

Thank you.
Jun 25 '14 #1
Share this Question
Share on Google+
10 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,483
When you Debug.Print, what is the result that you get? That will help us greatly!

BTW, please use Code tags when you post Code!
Jun 25 '14 #2

100+
P: 102
This what I get with a debug.print:

INSERT INTO [dbo_Notes] (partshortages_ID, date, [by], [note]) VALUES( 16, '6/25/2014', 'SClark', 'sdbsdfgsdfgsdfgsdfgfdsg')
Jun 25 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,483
sc5502,

I am assuming [dboNotes] is the table? If so, this should not be in brackets.

Your fields: (partshortages_ID, date, [by], [note]) also should not be in parentheses.

Your date "may" require "#" before and after it.

So, this might help:

Expand|Select|Wrap|Line Numbers
  1. zSQL2 = _
  2. "INSERT INTO dbo_Notes " & _
  3. "partshortages_ID, date, [by], [note]" & _
  4. " VALUES (" & _
  5. " " & gblDBResult2 & _
  6. ", '" & RDateAdded & "'" & _
  7. ", '" & Rbuyer & "'" & _
  8. ", '" & Me.Notes & "')"
  9. Debug.Print zSQL2
  10. DoCmd.SetWarnings False
  11. DoCmd.RunSQL zSQL2
I've tried it without the "#", but if this still causes an error, try this for line 6:

Expand|Select|Wrap|Line Numbers
  1. ", #" & RDateAdded & "#" & _
Hope this hepps!
Jun 25 '14 #4

100+
P: 102
I've tried your the suggestions and still get the error.
Jun 25 '14 #5

Rabbit
Expert Mod 10K+
P: 12,430
Actually, the parentheses around the field list is required.

After you print out the SQL, put it in a query and see if it will run.
Jun 25 '14 #6

100+
P: 102
Here what I get


INSERT INTO dbo_Notes (partshortages_ID, date, by , note ) VALUES( 17, '6/25/2014', 'SClark', 'fhdfghdfghdfghdfghdfghdfghdfgh')


Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'by'.
Jun 25 '14 #7

twinnyfo
Expert Mod 2.5K+
P: 3,483
@Rabbit,

Yes, thank you--my mistake!

@sc5502,

"By" is a reserved word. Try putting it back into brackets.
Jun 25 '14 #8

Rabbit
Expert Mod 10K+
P: 12,430
date is also probably a key word, put that in brackets as well.
Jun 25 '14 #9

twinnyfo
Expert Mod 2.5K+
P: 3,483
I guess this is a good demonstration showing the importance of choosing good field names and avoiding reserved/key words!

I admit, I learned that the hard way.... :-/

Also, reviewing some of my sparse code that uses the INSERT INTO, you will have to enclose your date with "#":

Expand|Select|Wrap|Line Numbers
  1. ", #" & RDateAdded & "#" & _
Jun 25 '14 #10

NeoPa
Expert Mod 15k+
P: 31,769
As your [Date] field is designed as varchar(12) you actually won't want the # chars. Your SQL quotes there are already correct. Otherwise, the advice given so far, where not already corrected, should get you to where you want to be.

Good luck :-)
Jun 28 '14 #11

Post your reply

Sign in to post your reply or Sign up for a free account.