sql - 使用带有计算字段的 VBA 将记录插入 Access 2016 表

问题描述

我有一个旧的（旧的）Access 数据库来跟踪正在进行的工作信息。目前，最终用户在 Excel 中创建报价。如果无法完成报价，最终用户必须将电子表格中的信息复制/粘贴/手动输入到此 Access 数据库中。

Access 中有一个开始日期、一个停止日期和一个周转时间计算字段。周转时间是停止日期减去开始日期。我正在尝试自动化该过程，因此如果无法完成电子表格，他们可以单击一个按钮以使用输入的信息自动更新 Access 数据库，从而节省手动工作。

当我尝试运行代码时，我得到一个通用的：

“INSERT INTO 语句中的语法错误”。

这是代码：

    gblDatabaseLocation = "c:\UnderRevision\"
gblDatabaseName = "DLA WIP.accdb"

Dim CN As ADODB.Connection
Set CN = New ADODB.Connection
Dim sql As String

CN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & gblDatabaseLocation & gblDatabaseName

Set cm = New ADODB.Command
cm.ActiveConnection = CN


' load variables... from spreadsheet...
wID = "" ' will be autopopulated...
wReady = False ' default is N
wStatus = "OPEN" ' they want to select it manually
wInDate = Worksheets("SOLICITATION SUPPORT").Range("D6").value
wOutDate = "12/31/2019" ' blank for now...
wProgram = Worksheets("SOLICITATION SUPPORT").Range("D10").value
wBidPoint = Worksheets("SOLICITATION SUPPORT").Range("D18").value
wOwner = Worksheets("SOLICITATION SUPPORT").Range("D4").value
wPartNum = Worksheets("SOLICITATION SUPPORT").Range("D14").value
wQty = Worksheets("SOLICITATION SUPPORT").Range("D15").value
wSolicitation = Worksheets("SOLICITATION SUPPORT").Range("D11").value
wPRNUM = Worksheets("SOLICITATION SUPPORT").Range("D12").value
wNSN = Worksheets("SOLICITATION SUPPORT").Range("D13").value
wPending = "" ' want to choose it
wMultiPend = True ' want to choose it
wEstDollars = Worksheets("SOLICITATION SUPPORT").Range("AA6").value
wSupplier = "<SUPPLIER>" ' should read this in...
wSoleSource = "<SOLESOURCE>" ' varies based on solicitation
wFOB = Worksheets("SOLICITATION SUPPORT").Range("W10").value
wInspection = Worksheets("SOLICITATION SUPPORT").Range("W11").value
wMilStd130 = Worksheets("SOLICITATION SUPPORT").Range("N10").value
wShelfLife = "30" ' select later
wTime = "10" ' input later - shelf life called out in SOL
wTraceability = "Y" ' if approved = Y, pending = X
wSource = "<SOURCE>" ' from SOL
wSupplierPartNum = "<SUPPLIERPARTNUM>" ' from SOL
wIAW = "<IAW>" ' select later
w50US = Worksheets("SOLICITATION SUPPORT").Range("N17").value
wISO = Worksheets("SOLICITATION SUPPORT").Range("N24").value
wJCP = Worksheets("SOLICITATION SUPPORT").Range("N25").value
wInspAtMFG = Worksheets("SOLICITATION SUPPORT").Range("N14").value
wQPL_QML = Worksheets("SOLICITATION SUPPORT").Range("N15").value
wFat = Worksheets("SOLICITATION SUPPORT").Range("N12").value
wMfgSymbol = Worksheets("SOLICITATION SUPPORT").Range("N11").value
wSBSA = Worksheets("SOLICITATION SUPPORT").Range("N22").value
wCOQC = Worksheets("SOLICITATION SUPPORT").Range("N16").value
wNotes = Worksheets("SOLICITATION SUPPORT").Range("A31").value
wPKey = "" ' pull in later
wQuoted = True
wTat = DateDiff("D", wInDate, wOutDate)

wNotNotified = "<NOT NOTIFIED>"

' create sql string...
sql = "INSERT INTO WIP ( Ready, Status, [In Date], [Out Date], Program, [Bid Point], Owner, [Part #], Qty, Solicitation, [PR #], " _
    & "NSN, Pending, [Multi-Pend], [Est $], Supplier, [Sole Source], FOB, Inspection, MilStd130, [Shelf Life], Time, Traceability, Source, " _
    & "[Supplier Part #], IAW, [50% US], ISO, JCP, [Insp@Mfg], [QPL-QML], Fat, [Mfg Symbol], SBSA, COQC, Notes, PKEY, Quoted, tat, " _
    & "[Not Notified By PCF]) VALUES " _
    & "('" & wReady & "','" & wStatus & "',#" & wInDate & "#,#" & wOutDate & "#,'" & wProgram & "','" & wBidPoint & "','" _
    & wOwner & "','" & wPartNum & "','" & wQty & "','" & wSOlication & "','" & wPRNUM & "','" & wNSN & "','" & wPending & "','" & wMultiPend & "'," _
    & wEstDollars & ",'" & wSupplier & "','" & wSoleSource & "','" & wFOB & "','" & wInspection & "','" & wMilStd130 & "','" & wShelfLife & "','" _
    & wTime & "','" & wTraceability & "','" & wSource & "','" & wSupplierPartNum & "','" & wIAW & "','" & w50US & "','" & wISO & "','" _
    & wJCP & "','" & wInspAtMFG & "','" & wQPL_QML & "','" & wFat & "','" & wMfgSymbol & "','" & wSBSA & "','" & wCOQC & "','" & wNotes & "','" _
    & wPKey & "','" & wQuoted & "'," & wTat & ",'" & wNotNotified & "')"

cm.CommandText = sql
Set rs1 = New ADODB.Recordset
Set rs1 = CN.Execute(sql, varparams, adCmdText)

这是我的代码创建的 SQL 语句：

INSERT INTO WIP ( Ready, Status, [In Date], [Out Date], Program, [Bid Point], Owner, [Part #], Qty, Solicitation, [PR #], NSN, Pending, [Multi-Pend], [Est $], Supplier, [Sole Source], FOB, Inspection, MilStd130, [Shelf Life], Time, Traceability, Source, [Supplier Part #], IAW, [50% US], ISO, JCP, [Insp@Mfg], [QPL-QML], Fat, [Mfg Symbol], SBSA, COQC, Notes, PKEY, Quoted, tat, [Not Notified By PCF]) VALUES ('False','OPEN',#10/30/2018#,#12/31/2019#,'OSHKOSH','OSHKOSH','ALI ZERBE','1212FX','1','','','','','True',4,'<SUPPLIER>','<SOLESOURCE>','','','','30','10','Y','<SOURCE>','<SUPPLIERPARTNUM>','<IAW>','','','','','','','','','','','','True',427,'<NOT NOTIFIED>')

我已经仔细检查了语法，很确定我在所有字符串周围都有单引号，日期周围是#。我知道对于 AutoNumbered ID 字段，我从字段列表和值列表中省略了它。

但是 SQL 中如何引用计算字段呢？我是否也省略它，让数据库进行计算？我在互联网上找不到任何关于此的参考...

平淡无奇的错误消息并没有将我指向任何特定的方向。

请帮忙？

标签： sqlvbams-access

sql - 使用带有计算字段的 VBA 将记录插入 Access 2016 表

问题描述

解决方案

推荐阅读