Insert a record into Access 2016 table using VBA with a calculated field

Question

I have a legacy (old) Access database that tracks work in progress information. Currently the end-user creates a quote in Excel. If the quote can't be completed, the end-user has to copy/paste/manually enter the info from the spreadsheet into this Access database.

There is a Start Date, a Stop Date, and a Turn-Around-Time calculated field in Access. The Turn-Around-Time is the Stop Date minus the Start Date. I'm trying to automate the process, so if the spreadsheet cannot be completed, they can click a button to automatically update the Access database with the information that was entered, saving the manual work.

When I attempt to run the code, I get a generic:

"Syntax error in INSERT INTO statement".

Here is the code:

    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)

Here is the SQL statement my code creates:

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>')

I've double checked the syntax, pretty sure I have single quotes around all the strings, #'s around the dates. I know that for the AutoNumbered ID field, I omit that from the list of fields, and the list of values.

But how is the Calculated Field referenced in the SQL? Do I omit it also, and let the database do the calculation? I cannot find any reference to this on the internet...

The bland error message isn't pointing me in any specific direction.

Help please?

Cannot set value in Access calculated field, it must calculate. Which field is it - tat? Omit it from the INSERT. — June7
The column Time also should be enclosed in square brackets. — Sergey S.

Steven Hanzek Steven Hanzek · Accepted Answer · 2018-11-01T00:22:48

Thanks for your responses. I ended up finding multiple issues, which i managed to work through: (1) Copied the query text and pasted into SSMS and attempted to execute it. First thing it flagged was the field 'Source'. Apparently that is a reserved word in SQL. Changed it to Srce. Replaced the '#' around the dates with apostrophes. Changed the True's to 1 and the Falses to 0. Was able to execute it in SSMS. (2) This syntax, however, wasn't compatible with the Linked Tables approach, so I found the correct ODBC connection string and bypassed the Linked Tables to go more directly to the SQL server. This allowed me to use my modified query syntax. (3) Migrated the table to the SQL server instead of an access table. Eliminated the calculated field completely... Renamed all the fields to eliminate spaces, dashes, ampersands, dollar signs, anything that could cause any sort of issue...

So i now have it functioning! Thanks again. Steve

Insert a record into Access 2016 table using VBA with a calculated field

1 Answers