Assistance needed in sql syntax while breaking code into multiple lines

[Voyager]

Hi experts,
I am going to ask an pretty old question. Though I researched everywhere I could not figure out a solution and I strongly believe you can help. I am trying to use sql insert into statement to update a table with form data. But when I break the code into multiple lines I am getting some error. Here is the code.

Dim updsql as string
updsql = “ insert into tasks [received],[sender],[sent at] _
& “[employee],[department] _
& “ values (‘“ & me.rvd & “‘),(‘“ & me.snd & “‘),(‘“ & me.sntat & “‘)” _
& “(‘“ & me.emp & “‘),(‘“ & me.dpt & “‘));)

Getting error as 3134 syntax error in insert into statement
Where am I going wrong.

 

[Gasman]

No closing double quote on first and second line?
FWIW I hardly use the continuation character, but start a new line

updsql = updsql & " ....."

 

[theDBguy]

Hi. I would also be careful when you start the continuation line to make sure to insert or not insert a space to create a valid syntax.

 

[pbaldy]

Here's a common debugging method:

http://www.baldyweb.com/ImmediateWindow.htm

 

[The_Doc_Man]

Damn, three excellent suggestions and I missed the chance. Must have been busy elsewhere. Voyager, EVERY ONE of the three suggestions you got is totally valid.

 

[Voyager]

All four of you,
Thanks a ton for valuable inputs I think I’m close but not yet reached the solution
1) A’s Gasman suggested I have changed my syntax and dixed the quotes
2) I’m still getting the error
3) As pbaldy suggested I had debugged the code
4) Immediate window shows

insert into tasks [received],[sender],[sent at],[employee],[department] values [‘05-05-2019’], [‘test’],[‘test’],[‘test’],[‘test’];

Except for received field I have kept text as test for other fields.
Is there anything wrong in the quotation I use.? Or something else going wrong?

 

[isladogs]

Your syntax is still incorrect.
Enclose all fields and values in ().
You only need [] brackets around the field name with a space.
Do not use [] around the values.

Assuming all the fields are text then try this

insert into tasks (received, sender, [sent at], employee, department) values (‘05-05-2019’, ‘test’, ‘test’, ‘test’, ‘test’);

However if Received is a Date field then you need #05/05/2019#

 

[theDBguy]

All four of you,
Thanks a ton for valuable inputs I think I’m close but not yet reached the solution
1) A’s Gasman suggested I have changed my syntax and dixed the quotes
2) I’m still getting the error
3) As pbaldy suggested I had debugged the code
4) Immediate window shows

insert into tasks [received],[sender],[sent at],[employee],[department] values [‘05-05-2019’], [‘test’],[‘test’],[‘test’],[‘test’];

Except for received field I have kept text as test for other fields.
Is there anything wrong in the quotation I use.? Or something else going wrong?

If you take the result from the Immediate Window and paste it in the query designer, Access should tell you what’s wrong with it when you try to execute it, so you’ll know how to fix the code.

 

[Voyager]

Hi isladogs,
Thanks. I want to make one thing more clear which I didn’t update you properly.

I am using this code now

updsql = “ insert into tasks (received,sender,[sent at]”
updsql = updsql & “employee,department)”
updsql = updsql & “values (‘“ & me.rvd & “‘),(‘“ & me.snd & “‘),”
updsql = updsql & “(‘“ & me.sntat & “‘),” 
updsql = updsql & “(‘“ & me.emp & “‘),(‘“ & me.dpt & “‘);”

When I run this code I am getting the immediate window suggestion as follows

insert into tasks received,sender,[sent at],employee,department values [‘05-05-2019’], [‘test’],[‘test’],[‘test’],[‘test’];

your code IS right but I am referring form field values here.
Could you throw me more light on how can I refer the form field values here.

 

[Gasman]

That cannot be the case.?
You have ( in the code and [ in the result ?
Plus you surround ALL the values in one string with () not every one

So you are not giving us the correct data.

 

[isladogs]

Agree with Gasman.
The SQL you posted cannot possibly create that output in the immediate window.
1. The SQL has a missing comma after [Sent At] but its shown in the immediate output.
2. There is a space missing before Values in the SQL but it magically appears in the output
3. The [] brackets don't just appear around the values without being typed in the first place.
4. The () brackets should only be used as I showed you

Please copy and paste the exact SQL used and the exact output from the immediate window. Then one of us can give you the correct answer or guide you on how to do this yourself

 

[Gasman]

Clear the immediate window before running the code. That way you will get the latest version. Then post that.

 

[Voyager]

Immediate window response Hi Gasman, islodogs,
I am sorry I didn’t realise the immediate window was keep on adding the contents every time I run the debug code. Yes what I have you was older code. As you directed I cleared the contents and given you the update code and immediate window response

updsql = “ insert into tasks (received,sender,[sent at]”
updsql = updsql & “employee,department)”
updsql = updsql & “values (#” & me.rvd & “ #),(‘“ & me.snd & “‘),”
updsql = updsql & “(‘“ & me.sntat & “‘),” 
updsql = updsql & “(‘“ & me.emp & “‘),(‘“ & me.dpt & “‘);”

insert into tasks (received,sender,[sent at],employee,department) values (#05-05-2019#), (‘test’),(‘test’),(‘test’),(‘test’);

 

[pbaldy]

You don't want parentheses around each value, you want a single pair around the whole VALUES clause.

...VALUES(Field1, Field2,...)

 

[Voyager]

Ok this is how I changed the code and again error as runtime error 3134

updsql = “ insert into tasks (received,sender,[sent at]”
updsql = updsql & “employee,department)”
updsql = updsql & “values #” & me.rvd & “ #, ‘“ & me.snd & “‘,”
updsql = updsql & “‘“ & me.sntat & “‘,” 
updsql = updsql & “‘“ & me.emp & “‘,‘“ & me.dpt & “‘;”

Immediate

insert into tasks (received,sender,[sent at],employee,department) values (#05-05-2019#, ‘test’, ‘test’,‘test’,‘test’);

 

[isladogs]

Paul beat me to it.
You ignored my comments from post #11 about missing comma after [sent at], missing space before values and bracketing. Corrected version....

updsql = “ insert into tasks ( received, sender, [sent at],"
updsql = updsql & “ employee, department )”
updsql = updsql & “ values (#” & me.rvd & “#, ‘“ & me.snd & “‘,”
updsql = updsql & “ ‘“ & me.sntat & “‘,” 
updsql = updsql & “ ‘“ & me.emp & “‘, ‘“ & me.dpt & “‘);”

Or it can be written as

updsql = “ insert into tasks ( received, sender, [sent at], employee, department )” & _
“ values (#” & me.rvd & “#, ‘“ & me.snd & “‘, ‘“ & me.sntat & “‘,” & _
“ ‘“ & me.emp & “‘, ‘“ & me.dpt & “‘);”

Or this method not using VALUES...

updsql = “ insert into tasks ( received, sender, [sent at], employee, department )” & _
“ SELECT #” & me.rvd & “# AS received, ‘“ & me.snd & “‘ AS sender, ‘“ & me.sntat & “‘ AS [sent at],” & _
“ ‘“ & me.emp & “‘ AS employee, ‘“ & me.dpt & “‘ AS department;”

 

[Gasman]

Again, the code does not match the output.???

Why not just use the code posted in post #16 ?

 

[Voyager]

Gasman, dbguy, isladogs, docman... thank you thank you very much. As you rightly pointed out I have made a mistake in using square bracket. I mean I didn’t use square bracket in a field that’s where it went wrong. Now I am able to update the records. Thank you. You guys make this forum a reliable one. I’m able to make some significant progress in my project without any mentors just with the suggestions from you. Great.

 

[isladogs]

For the benefit of others reading this in the future, did you use one of the versions I posted in #16. If something else, please post the code you did use.

 

[Uncle Gizmo]

I regularly see people on the forums having difficulties with insert into SQL statements. I've blogged about it on my website.

Basically as with all VBA code you are better off if you break it down into smaller components, and also by following a set pattern. That's how I came up with my method of handling insert into SQL statements and indeed all my other SQL statements. They all consist of individual strings, and an easy to follow a basic pattern.

For more information see my website:- INSERT INTO SQL Statement


Example:-

Private Function fAddRec(txtFld1 As String, txtFld2 As String, txtFld3 As String, txtFld4 As Integer)

'Add Text Delimiters - Chr(34) = "
txtFld1 = Chr(34) & txtFld1 & Chr(34)
txtFld2 = Chr(34) & txtFld2 & Chr(34)

'Add Date Delimiters - Chr(35) = #
txtFld3 = Chr(35) & txtFld3 & Chr(35)

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblSimple_INSERT_INTO_SQL(fld1, fld2, fld3, fld4) "
strSQL2 = "Values(" & txtFld1 & ", " & txtFld2 & ", " & txtFld3 & ", " & txtFld4 & ")"

strSQL0 = strSQL1 & strSQL2

CurrentDb.Execute strSQL0

End Function      'fAddRec

As you can see in string "strSQL2" there are no delimiters in the string, the string is a concatenation of the values. The values have the delimiters added outside of the SQL Statement keeping the SQL Statement simple and easy to duplicate. If you want to add more columns, just copy and paste, rename. No fiddly "apostrophes" "double quotes" "hash" that's all handled separately.

"strSQL1" is also very easy to create, all you do is create the query, then copy the field names out of the query and paste them into here (fld1, fld2, fld3, fld4)... It's on the cards for me to add some videos to my blog to explain this bit, haven't done that yet though!