问题描述

以下代码创建一个 SQL 字符串，该字符串在 MS Access 中产生语法错误 (3134).

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

产生语法错误的sql"字符串的值为:

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

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

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

表名和字段名是正确的.desc"和descExtended"字段是文本类型.itemNumber"和currentPrice"是数字.

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

推荐答案

这是您的字段名称.DESC 在 SQL 中是降序而不是描述.DESC 是 SQL 语法中的保留字.您需要将其放入 [] 或更改它.(如果还为时不晚，我会推荐后者，以免将来头痛.)避免使用保留字作为表名或字段名.

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)

或更好

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

这篇关于VBA 运行时错误 3134的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！