VBA SQL字符串运行时错误3075 [英] VBA SQL String Run-Time Error 3075

问题描述

我有一个向表中添加数据的字符串，因此我可以从数据中打印报告或标签.数据由地址组成，由于地址中的逗号，导致字符串失败.这个字符串一直在工作，但是当它有一些奇怪的地址时，我认为这就是造成此问题的原因.

I have a string that is adding data to a table so I can print a report or labels from the data. The data consists of addresses and is causing the string to fail because of the comma in the address. this string has been working but when it has some weird addresses I think that is what is causing this.

sqls = "INSERT INTO tInvReportDataWrk(SO,ITEM,QTY,billTO,shipTO,LINEKEY)VALUES('" & 
  SO & "', '" & it & "', '" & qty & "', '" & billTO & "', '" & shipTO & "', '" & lk & "')"

正在尝试的数据来自debug.print

INSERT INTO tInvReportDataWrk(SO,ITEM,QTY,billTO,shipTO,LINEKEY)
VALUES('0000001', 'L-R_4-8R2B-01', '2', 'BAR - ANAHEIM
BAR BRANCH
P.O. BOX 00000
VENDOR ID# VC-00001
Saint Louis, MO  00008
', 'ABC ELEMENT WAREHOUSE
2000 O'TOOL AVE.
San Jose, CA  95131-1301
', '000001')

推荐答案

我不确定地址中的逗号是否有问题.在我看来， O'TOOL 中的撇号应该是一个问题.但是，如果这不是Access抱怨的第一个错误的原因，则应在解决第一个错误后触发另一个错误.

I'm uncertain whether the comma in the address is the problem. It looks to me like the apostrophe in O'TOOL should be a problem. But if it is not the cause of the first error Access complains about, it should trigger another error after you fix the first error.

对于一个简单的示例，以下代码触发错误3075，查询表达式"O'TOOL');'中的语法错误(缺少运算符)".

For a simple example, the following code triggers error 3075, "Syntax error (missing operator) in query expression ''O'TOOL');'."

Dim strInsert As String
strInsert = "INSERT INTO tblFoo(text_field) VALUES ('O'TOOL');"
CurrentDb.Execute strInsert, dbFailOnError

将INSERT语句更改为其中任意一个，即可使该语句执行而不会出错.

Changing the INSERT statement to either of these allows the statement to execute without error.

strInsert = "INSERT INTO tblFoo(text_field) VALUES ('O''TOOL');"
strInsert = "INSERT INTO tblFoo(text_field) VALUES (""O'TOOL"");"

您可以修改代码以使用其中一种方法.但是，请考虑参数查询或 DAO.Recordset.AddNew 方法...然后引号和撇号将不再那么麻烦.

You could revise your code to use one of those approaches. However consider a parameter query or the DAO.Recordset.AddNew method instead ... and then quotes and apostrophes will be less troublesome.

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