Access/VBA Question

[sc5502]

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.

zSQL2 = _
    "INSERT INTO [dbo_Notes] " & _
        "(partshortages_ID, date, [by], [note])" & _
        " VALUES(" & _
        "  " & gblDBResult2 & _
        ", '" & RDateAdded & "'" & _
        ", '" & Rbuyer & "'" & _
        ", '" & Me.Notes.Value & "')"
 Debug.Print zSQL2
DoCmd.SetWarnings False
DoCmd.RunSQL zSQL2

The database definitions are:
partshortages_I D int
Date varchar(12)
By varchar(50)
note text

Thank you.

[twinnyfo]

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!

[sc5502]

This what I get with a debug.print:

INSERT INTO [dbo_Notes] (partshortages_ ID, date, [by], [note]) VALUES( 16, '6/25/2014', 'SClark', 'sdbsdfgsdfgsdf gsdfgfdsg')

[twinnyfo]

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:

zSQL2 = _
"INSERT INTO dbo_Notes " & _
"partshortages_ID, date, [by], [note]" & _
" VALUES (" & _
" " & gblDBResult2 & _
", '" & RDateAdded & "'" & _
", '" & Rbuyer & "'" & _
", '" & Me.Notes & "')"
Debug.Print zSQL2
DoCmd.SetWarnings False
DoCmd.RunSQL zSQL2

I've tried it without the "#", but if this still causes an error, try this for line 6:

", #" & RDateAdded & "#" & _

Hope this hepps!

[sc5502]

I've tried your the suggestions and still get the error.

[Rabbit]

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.

[sc5502]

Here what I get


INSERT INTO dbo_Notes (partshortages_ ID, date, by , note ) VALUES( 17, '6/25/2014', 'SClark', 'fhdfghdfghdfgh dfghdfghdfghdfg h')


Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'by'.

[twinnyfo]

@Rabbit,

Yes, thank you--my mistake!

@sc5502,

"By" is a reserved word. Try putting it back into brackets.

[Rabbit]

date is also probably a key word, put that in brackets as well.

[twinnyfo]

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 "#":

", #" & RDateAdded & "#" & _

[NeoPa]

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 :-)