语句错误结束(End of statement error)

我想组装多换行的INSERT语句。 插入包括数值以及文本数据。 我不断收到一个错误，我无法找到我的语法错误。 任何人都可以看到我的错误？

CurrentDb.Execute "INSERT INTO tblCustParts (CustPartNum,CustomerID,Alloy,Temper,Finish,FormType,Gauge,GaugeAimPlus,GaugeAimMinus," & _
        "GaugeGuarPlus,GaugeGuarMinus,Width,WidthAimPlus,WidthAimMinus,WidthGuarPlus,WidthGuarMinus,Length,LengthAimPlus,LengthAimMinus," & _
        "LengthGuarPlus,LengthGuarMinus,Diameter,DiameterAimPlus,DiameterAimMinus,DiameterGuarPlus,DiameterGuarMinus,CircleShear,IDMin," & _
        "ODMin,ODMax,LabelAs,ProduceAs,ShopPaperNotes,EyeOrientation,CoreType,PackingNotes,AQ,PaperInterleave,HeatTreatedSkids,HasRecipeFlag," & _
        "QRRExists,TensionLevel,MSKCoreSpec,ChemCertsReq,PhysCertsReq,PhysAndChemCertsReq,AAStandard,ASTM_B209_10)" & _
        "VALUES('" & Part & "', " & Me.Customer & ", '" & Alloy & "', '" & Temper & "', '" & Finish & "', '" & FormType & "', " & Me.Gauge & ", "" & _
        " & Me.Gauge & ", " & Me.GaugeAimPlus & ", " & Me.GaugeAimMinus & ", " & Me.GaugeGuarPlus & ", " & Me.GaugeGuarMinus & ", "" & _
        " & Me.WidthEntry & ", " & Me.WidthAimPlus & ", " & Me.WidthAimMinus & ", " & Me.WidthGuarPlus & ", " & Me.WidthGuarMinus & ", "" & _
        " & Me.LengthEntry & ", " & Me.LengthAimPlus & ", " & Me.LengthAimMinus & ", " & Me.LengthGuarPlus & ", " & Me.LengthGuarMinus & ", "" & _
        " & Me.Diameter & ", " & Me.DiameterAimPlus & ", " & Me.DiameterAimMinus & ", " & Me.DiameterGuarPlus & ", "" & _
        " & Me.DiameterGuarMinus & ", " & Me.CS & ", " & Me.IDMin & ", " & Me.ODMin & ", " & Me.ODMax & ", '" & Me.LabelAs & "', '"" & _
        " & Me.ProduceAs & "', '" & Me.ShopPaperNotes & "', '" & Me.EyeOrientation & "', '" & Me.CoreType & "', '" & Me.PackingNotes & "', "" & _
        " & Me.AQ & ", " & Me.PaperInterleave & ", " & Me.HeatTreatedSkids & ", " & Me.HasRecipeFlag & ", " & Me.QRRExists & ", "" & _
        " & Me.TensionLevel & ", " & Me.MSKCoreSpec & ", " & Me.ChemCertsReq & ", " & Me.PhysCertsReq & ", " & Me.PhysAndChemCertsReq & ", "" & _
        " & Me.AAStandard & ", " & Me.ASTM_B209_10 & ")"

Answer 1:

我不知道什么额外的引号和＆是，但他们造成的问题。 它们出现在每个值线的起点和终点。 最好是把你的字符串，在一个单独的变量，然后执行，更容易发现问题。 你可能也想考虑的参数，它们会让你的生活在这种情况下更容易。

ssql = "INSERT INTO tblCustParts (CustPartNum,CustomerID,Alloy,Temper,Finish,FormType,Gauge,GaugeAimPlus,GaugeAimMinus," & _
        "GaugeGuarPlus,GaugeGuarMinus,Width,WidthAimPlus,WidthAimMinus,WidthGuarPlus,WidthGuarMinus,Length,LengthAimPlus,LengthAimMinus," & _
        "LengthGuarPlus,LengthGuarMinus,Diameter,DiameterAimPlus,DiameterAimMinus,DiameterGuarPlus,DiameterGuarMinus,CircleShear,IDMin," & _
        "ODMin,ODMax,LabelAs,ProduceAs,ShopPaperNotes,EyeOrientation,CoreType,PackingNotes,AQ,PaperInterleave,HeatTreatedSkids,HasRecipeFlag," & _
        "QRRExists,TensionLevel,MSKCoreSpec,ChemCertsReq,PhysCertsReq,PhysAndChemCertsReq,AAStandard,ASTM_B209_10)" & _
        "VALUES('" & Part & "', " & Me.Customer & ", '" & Alloy & "', '" & Temper & "', '" & Finish & "', '" & FormType & "', " & Me.Gauge & ", " & _
        Me.Gauge & ", " & Me.GaugeAimPlus & ", " & Me.GaugeAimMinus & ", " & Me.GaugeGuarPlus & ", " & Me.GaugeGuarMinus & ", " & _
        Me.WidthEntry & ", " & Me.WidthAimPlus & ", " & Me.WidthAimMinus & ", " & Me.WidthGuarPlus & ", " & Me.WidthGuarMinus & ", " & _
        Me.LengthEntry & ", " & Me.LengthAimPlus & ", " & Me.LengthAimMinus & ", " & Me.LengthGuarPlus & ", " & Me.LengthGuarMinus & ", " & _
        Me.Diameter & ", " & Me.DiameterAimPlus & ", " & Me.DiameterAimMinus & ", " & Me.DiameterGuarPlus & ", " & _
        Me.DiameterGuarMinus & ", " & Me.CS & ", " & Me.IDMin & ", " & Me.ODMin & ", " & Me.ODMax & ", '" & Me.LabelAs & "', '" & _
        Me.ProduceAs & "', '" & Me.ShopPaperNotes & "', '" & Me.EyeOrientation & "', '" & Me.CoreType & "', '" & Me.PackingNotes & "', " & _
        Me.AQ & ", " & Me.PaperInterleave & ", " & Me.HeatTreatedSkids & ", " & Me.HasRecipeFlag & ", " & Me.QRRExists & ", " & _
        Me.TensionLevel & ", " & Me.MSKCoreSpec & ", " & Me.ChemCertsReq & ", " & Me.PhysCertsReq & ", " & Me.PhysAndChemCertsReq & ", " & _
        Me.AAStandard & ", " & Me.ASTM_B209_10 & ")"


Dim db As database
Set db = CurrentDB
db.Execute ssql dbFailOnError

最后一条记录插入ID

Set rs = db.OpenRecordset("select @@identity as id")
LastID = rs("id")

参数例如：

ssql = "INSERT INTO Table1 (Atext,Anumber) Values (@AText,@Anumber)"
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", ssql)
qdf.Parameters("@atext") = "abc"
qdf.Parameters("@Anumber") = 1
qdf.Execute dbFailOnError

最后一条记录插入ID

qdf.SQL = "select @@identity as id"
Set rs = qdf.OpenRecordset
LastID = rs("id")

文章来源: End of statement error