缺少逗号错误通过access 2010 vba插入sql错误

Question

遇到了最奇怪的问题。我确实有一个sql插入语句，在sql中正确工作。当我把sql到vba它工作得很好从我的电脑。然而，它不工作，并显示sql错误:缺少逗号。问题出在哪里?我使用Access 2010 plus，其他人使用相同的Access版本，并具有相同的ODB连接(DSN服务器)。一些代码示例:

   sql = "Driver={Microsoft ODBC for Oracle}; " & _
        "CONNECTSTRING=(DESCRIPTION=" & _
        "(ADDRESS=(PROTOCOL=TCP)" & _
        "(HOST= ODB)(PORT=1520))" & _
        "(CONNECT_DATA=(SERVICE_NAME=ABTL))); uid=IN; pwd=XXX;"
  Set con = New ADODB.Connection
  Set rec = New ADODB.Recordset
  Set cmd = New ADODB.Command
  con.Open sql
        Set db = CurrentDb()
        Set rst = db.OpenRecordset(strSQL)
Do While Not rst.EOF
insetSQL = con.Execute(" INSERT INTO STOCK (PNM_AUTO_KEY, PN, DESCRIPTION, HISTORICAL_FLAG, qty_oh, qty_adj, qty_available, CTRL_NUMBER, CTRL_ID, receiver_number, rec_date, serial_number,pcc_auto_key, cnc_auto_key, loc_auto_key, whs_auto_key, unit_cost, adj_cost, stc_auto_key, visible_mkt, remarks, ifc_auto_key, exp_date, unit_price, tagged_by, tag_date, owner, PART_CERT_NUMBER,ORIGINAL_PO_NUMBER, SHELF_LIFE, CTS_AUTO_KEY, MFG_LOT_NUM, AIRWAY_BILL )" & _
                                    "  VALUES ( " & rst![minimumas] & ", '" & rst![pn] & "', '" & "FROM_SCRIPT" & "', '" & "F" & "'," & rst![qty_oh] & "," & rst![qty_oh] & "," & rst![qty_oh] & "," & "G_STM_CTRL_NUMBER.nextval" & "," & "1" & ", '" & rst![receiver_number] & "', " & " TO_DATE('" & rst![rec_date] & "','YYYY-MM-DD'), '" & rst![serial_number] & "'," & rst![pcc_auto_key] & "," & rst![cnc_auto_key] & "," & rst![loc_auto_key] & "," & rst![whs_auto_key] & "," & rst![unit_cost] & "," & rst![unit_cost] & "," & "1" & ",'" & "T" & "', '" & rst![remarks] & "'," & _
                                      "1" & ", " & " TO_DATE('" & rst![exp_date] & "','YYYY-MM-DD'), " & "0" & ",'" & rst![TAGGED_BY] & "', " & " TO_DATE('" & rst![tag_date] & "','YYYY-MM-DD'), '" & "" & "', '" & rst![PART_CERT_NUMBER] & "', '" & rst![ORIGINAL_PO_NUMBER] & "', '" & rst![SHELF_LIFE] & "', " & rst![CERT_SOURCE] & ", '" & rst![MFG_LOT_NUM] & "', '" & rst![AIRWAY_BILL] & "'" & " )")
Loop 

Answer 1

可能是其中一个记录集字段是字符串数据类型，其值包含逗号，但您没有将其作为字符串处理。

如

VALUES ( " & rst![minimumas] & ",

要使以上代码正常工作，最小值不能包含任何逗号。

您确定记录集中的所有字符串字段都已添加到SQL字符串中，并在它们周围加上'吗?

我们需要看到sql字符串被创建，然后导致错误。请按要求提供。

=========================================================

PART 2查看导致错误的SQL

代替

 insetSQL = con.Execute(

,错误继续下一个strMySQl =执行语句中当前使用的字符串insetSQL = con.Execute(strMySQl)如果犯错。然后编号<> 0停止调试。打印错误。数,err.description调试。打印strMySQl如果

然后将在当前窗口中打印的SQL字符串发送到该站点。

我还建议您将在您的机器上生成的SQL字符串与其他机器进行比较-以防发生奇怪的事情。