VBA Run-time error 3134

Question

The following code creates a SQL string which produces a syntax error (3134) in MS Access.

        sql = "INSERT INTO tblItems (desc, descExtended, itemNumber, currentPrice) " & _
                     "VALUES (" & _
                     "'" & rs.Fields("Field6") & "', " & _
                     "'" & rs.Fields("Field7") & "', " & _
                     rs.Fields("Field1") & ", " & _
                     rs.Fields("Field8") & _
                     ")"
        db.Execute sql, dbFailOnError

The value of the "sql" string which produces the syntax error is:

"INSERT INTO tblItems (desc, descExtended, itemNumber, currentPrice) VALUES ('APPLE GRANNY SMITH SLI IQF', 'GEMS OF FRUIT', 2050791, 49)"

The table and field names are correct. The "desc" and "descExtended" fields are of type Text. "itemNumber" and "currentPrice" are Number.

Answer 1

It's your field name. DESC is descending in SQL not description. DESC is a reserved word in SQL syntax. You will either need to put it in [] or change it. (I'd recommend the latter if its not too late to save future headache.) Avoid using reserved words as table or field names.

INSERT INTO tblItems ([desc], descExtended, itemNumber, currentPrice) 
VALUES ('APPLE GRANNY SMITH SLI IQF', 'GEMS OF FRUIT', 2050791, 49)

or better

INSERT INTO tblItems (Descript, descExtended, itemNumber, currentPrice) 
VALUES ('APPLE GRANNY SMITH SLI IQF', 'GEMS OF FRUIT', 2050791, 49)