Can't insert record having varChar(Max) field

Question

I have an Access front-end linked to a SQL Server Express back-end and have no issue inserting a record using the following statement:

strSQL = "INSERT INTO tblTrans (xGUID, TransDate, TransType, Note, PatientID, UserName) " & _
'"VALUES ('" & sGUID & "',#" & dtDateTime & "#,'Clinical Note','" & strNoteBB & "', '" & strDriverID & "','AutoUser')"

However, if I simply add the varChar(Max) field named "Note" to my statement, I get a 3134 Syntax Error. 

strSQL = "INSERT INTO tblTrans (xGUID, TransDate, TransType, Note, PatientID, UserName) " & _
'"VALUES ('" & sGUID & "',#" & dtDateTime & "#,'Clinical Note','This is a note'" & strDriverID & "','AutoUser')"

Any ideas?  Thanks.

Answer 1

Hi,

This error will comes when you use SQL reserved words in your query. Try to find that column

Solution:

For E.g, Let say Note is that column, then the solution is Putting the Brackets to your [] column like [Note]

See this solution

---

JAYENDRAN

Answer 2

JAYENDRAN,

SUCCESS!  THANKS FOR YOUR HELP.

KENRAV

Answer 3

Happy to hear that Kenrav! You could also upvote my answer if you like it.

---

JAYENDRAN

Answer 4

One thing I noticed...

There is no comma after your 'This is a note'". That means the note and the Driver ID are going in as one column. Therefore, your INSERT fields do not match your VALUES fields.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 5

Bill,

Excellent catch! 

FYI, my code actually had the comma in it but I accidentally erased it after pasting and modifying it (I didn't want to display the real note.)

My posted code, per your observation, would have indeed created an issue.  As such, your reply deserves a vote as well.  Thanks again for your eagle eye!

Kenrav

Answer 6

Glad to be of help. Little things like that often slip through the debugging process.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals