为什么我必须在INSERT和UPDATE语句中使用单引号和双引号 [英] Why do I have to use single and double quotes on INSERT and UPDATE statements
问题描述
在VBA for Access 2016中编写一些代码时，我必须对语句使用单引号和双引号。 有没有办法我可以这样做而不必使用它们？ 如果我不使用它们，则会出现一个对话框，提示我输入VALUES子句中的数据
或SET fieldname = fields之后的数据。 数据来自2个不同的记录集，我用它们加载到另一个表中。
While writing some code in VBA for Access 2016, I have to use the single and double quotes for the statements. Is there a way I can do this without having to use them? If I do not use them, then a dialog box displays prompting me for the data in the VALUES clause or after the SET fieldname = fields. The data comes from 2 different recordsets that I am using to load into a different table.
DoCmd.RunSQL" INSERT INTO tblVehicle（DlrName，Count，Model_N，Year，Make，Model，Model_Combo，Series_Combo，Engine，"& _
  " CityMPG，HwyMPG，传输，Invc_No_Shipping，Msrp_No_Shipping，Percentage_Add，Dollar_Add，航运，Addendum_Pgk_Name，"&放大器; _
      &NBSP ;" Addum_Price，Addum_Costs，SIR_Mths_36，SIR_Rate_36，SIR_Mths_48，SIR_Rate_48，SIR_Mths_60，SIR_Rate_60，"&安培; _
       " SIR_Mths_72， SIR_Rate_72，SIR_Mths_84，SIR_Rate_84）" &安培; _
            "VALUES（'"& rs1！DlrName&"'，'"& rs2！Count&"'，'"& rs2！ModelNumber&"'，'"& ; rs2！YearCar&"'，"& _
"'"& rs2！MakeCar&" ;'，'"& rs2！Model&"'，'"& rs2！ModelCombo&"'，"& rs2！Series&"'，'"& rs2！CarEngine&"'，
"& _
"'"& rs2！CityMPG &安培;" ' '"&安培; RS2 HwyMPG&安培;！"'， '"&安培;！RS2 CarTransmission&安培;"'， '"&安培;！RS2 InvcNoShipping&安培;"'，' " ;& rs2！MsrpNoShipping
&"'，"& _
"'"& rs1！AdvAddInvc&"'，'"& wrkDollar_Add& amp;"'，'"& rs2！Shipping&"'，'" &安培; rs1！OtdAddNameDesc& "，"，" &安培; _
            """ &安培; rs1！OtdPriceAmt& "'，'" &安培; rs1！OtdAddTax& "'，'" &安培; rs1！SIR_Mths_36& "'，'" &安培; rs1！SIR_Rate_36& "'，'" &安培; rs1！SIR_Mths_48
& "'，'" &安培; rs1！SIR_Rate_48& "，"，" &安培; _
            """ &安培; rs1！SIR_Mths_60& "'，'" &安培; rs1！SIR_Rate_60& "'，'" &安培; rs1！SIR_Mths_72& "'，'" &安培; rs1！SIR_Rate_72& "'，'" &安培; rs1！SIR_Mths_84
& "'，'" &安培; rs1！SIR_Rate_84& "'）;"
DoCmd.RunSQL "INSERT INTO tblVehicle(DlrName, Count, Model_N, Year, Make, Model, Model_Combo, Series_Combo, Engine, " & _
"CityMPG, HwyMPG, Transmission, Invc_No_Shipping, Msrp_No_Shipping, Percentage_Add, Dollar_Add, Shipping, Addendum_Pgk_Name, " & _
"Addum_Price, Addum_Costs, SIR_Mths_36, SIR_Rate_36, SIR_Mths_48, SIR_Rate_48, SIR_Mths_60, SIR_Rate_60, " & _
"SIR_Mths_72, SIR_Rate_72, SIR_Mths_84, SIR_Rate_84) " & _
"VALUES ('" & rs1!DlrName & "', '" & rs2!Count & "', '" & rs2!ModelNumber & "', '" & rs2!YearCar & "', " & _
"'" & rs2!MakeCar & "', '" & rs2!Model & "', '" & rs2!ModelCombo & "', '" & rs2!Series & "', '" & rs2!CarEngine & "',
" & _
"'" & rs2!CityMPG & "', '" & rs2!HwyMPG & "', '" & rs2!CarTransmission & "', '" & rs2!InvcNoShipping & "', '" & rs2!MsrpNoShipping
& "', " & _
"'" & rs1!AdvAddInvc & "', '" & wrkDollar_Add & "', '" & rs2!Shipping & "', '" & rs1!OtdAddNameDesc & "', " & _
"'" & rs1!OtdPriceAmt & "', '" & rs1!OtdAddTax & "', '" & rs1!SIR_Mths_36 & "', '" & rs1!SIR_Rate_36 & "', '" & rs1!SIR_Mths_48
& "', '" & rs1!SIR_Rate_48 & "', " & _
"'" & rs1!SIR_Mths_60 & "', '" & rs1!SIR_Rate_60 & "', '" & rs1!SIR_Mths_72 & "', '" & rs1!SIR_Rate_72 & "', '" & rs1!SIR_Mths_84
& "', '" & rs1!SIR_Rate_84 & "');"
推荐答案
不使用引号的唯一方法是设置一个固定的查询对象（即出现在导航窗格中。
The only way to not use quotes is to instead set up a fixed query object (that will appear in the navigation pane).
使用查询设计器功能并设置更新查询并使用名称保存。
Use the query designer feature and set up your update query and save it with a name.
然后在vba中 - 您可以使用Docmd打开该命名查询。 这将触发它运行。
Then in vba - you can use Docmd to open that named query. This will fire it to run.
这是一种完全不同的方法 - 我更喜欢这种方法，因为在故障排除方面，现在可以轻松地单独触发独立查询。
It is an entirely different approach - one that I prefer because in terms of trouble shooting that stand alone query now can be triggered all by itself easily.
这篇关于为什么我必须在INSERT和UPDATE语句中使用单引号和双引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
