Syantax error in SQL statement in VBA of MS Access

Question

I wrote code:

str = "INSERT INTO prod_LJ_Completion VALUES (" _
    & 0 & ", " & PartID & ", " & cmbLJ_TypeID.value & ", " & isChk(chkLJ_Transformator.value) & ", " _
    & isChk(chkLJ_LAD.value) & ", " & txtLJ_KevlarCable.value & ", " & txtLJ_GrayCable.value & ", " _
    & txtLJ_WhiteCable.value & ", " & cmbLJ_CylinderTypeID.value & ", " & isChk(chkLJ_JackPack.value) & ", " _
    & txtJackPackID.value & ", " & isChk(chkLJ_Completed) & ", " & txtLJ_CompletionDate.value & ", " _
    & 0 & ", " & "dbo" & ", " & txtLegLength.value & ", " & txtInsertDiameter.value & ", " & txtPlateKits.value & ", " _
    & isChk(chkPipe_Gal.value) & ", " & isChk(chkDemoModel.value) & ", " & 0 & ", " & 0 & ", " & txtComments.value & ", " & txtAA.value & ")"
CurrentProject.Connection.Execute str

When this code is executed I get error: "Syntax error in INSERT INTO statement".

What is wrong?

Answer 1

It looks like you are not properly quoting string values. E.g.,

& "dbo" &

should be

& "'dbo'" &

The same needs to be done for any other string data types.

Answer 2

I think you are finding your code difficult to debug because of that monster line that builds SQL text on the fly.

Rather than dynamically building SQL at run time, consider instead creating a procedure with strongly typed parameters in the database at design time then invoking the procedure with parameter values at run time. Adding a column list to your INSERT INTO statement may help too.

For example, as a one off exercise create you procedure, something like this (guessing column names and types):

CREATE PROCEDURE Add_prod_LJ_Completion
(
 :PartID INTEGER, 
 :LJ_TypeID INTEGER, 
 :LJ_Transformator YESNO, 
 :LJ_LAD YESNO, 
 :LJ_KevlarCable VARCHAR(10), 
 :LJ_GrayCable VARCHAR(20), 
 :LJ_WhiteCable VARCHAR(30), 
 :LJ_CylinderTypeID INTEGER, 
 :LJ_JackPack YESNO,  
 :JackPackID INTEGER, 
 :LJ_Completed YESNO,  
 :LJ_CompletionDate DATETIME, 
 :LegLength DECIMAL(11, 3), 
 :InsertDiameter DECIMAL(9, 5), 
 :PlateKits CHAR(5), 
 :Pipe_Gal YESNO,  
 :DemoModel YESNO,  
 :Comments MEMO, 
 :AA CHAR(10)
)
AS
INSERT INTO prod_LJ_Completion 
(
 const_1, PartID, LJ_TypeID, LJ_Transformator, 
 LJ_LAD, LJ_KevlarCable, LJ_GrayCable, 
 LJ_WhiteCable, LJ_CylinderTypeID, 
 LJ_JackPack, JackPackID, LJ_Completed, LJ_CompletionDate, 
 const_2, const_3, LegLength, InsertDiameter, PlateKits, 
 Pipe_Gal, DemoModel, const_4, const_5, Comments, AA

)
VALUES 
(
 0, :PartID, :LJ_TypeID, :LJ_Transformator, 
 :LJ_LAD, :LJ_KevlarCable, :LJ_GrayCable, 
 :LJ_WhiteCable, :LJ_CylinderTypeID, 
 :LJ_JackPack, :JackPackID, :LJ_Completed, :LJ_CompletionDate, 
 0, 'dbo', :LegLength, :InsertDiameter, :PlateKits, 
 :Pipe_Gal, :DemoModel, 0, 0, :Comments, :AA
);

At run time you can use a ADODB Command object to execute the procedure with parameters that reference your front end controls, something like this:

Sub runitwithparams()

  Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command
  With cmd
    Set .ActiveConnection = CurrentProject.Connection
    .NamedParameters = True
    .CommandType = adCmdStoredProc
    .CommandText = "Add_prod_LJ_Completion"

    .Parameters.Append = _
        .CreateParameter(":PartID", adInteger, adParamInput, , _
            PartID)
    .Parameters.Append = _
        .CreateParameter(":LJ_TypeID", adInteger, adParamInput, , _
            cmbLJ_TypeID.Value)
    .Parameters.Append = _
        .CreateParameter(":LJ_Transformator", adInteger, adParamInput, , _
            isChk(chkLJ_Transformator.Value))
   '// ...
   '// etc etc
   '// ...
    .Parameters.Append = _
        .CreateParameter(":AA", adChar, adParamInput, 10, _
            txtAA.Value)

    Dim rowsAffectedTally As Long
    .Execute rowsAffectedTally

  End With

End Sub

Also, your table seems quite 'wide'. Consider the design principle that a table EITHER models an entity OR a relationship between entities but not both. Your table has column names postfixed with ID, which suggest multiple relationships, and attribute names (e.g. LegLength) that suggests entities. Consider modelling one entity type per table and relationships each in a separate table.