Your first mistake is concatenating strings to create your sql statement - see the comment above from @Richard-Deeming. Use Parameterised queries instead. An example could look like this (NB this has not been tested because you haven't shared enough information about your tables or data)
Sub demo()
Dim sql As String
sql = "UPDATE ([WBS Tasks] " & _
"SET (A.[Title] = ?, " & _
"[Assigned To Engineer] = (Select ID from [Engineers (master)] Where [Employee Name] =?)," & _
"[Planned Eng Hours] = ?, [Planned Variance Hours] = 0, [WBS Element] =?, " & _
"[Description] = ?, [ProductLine] = ?, [Due Date] = , [Modified] = ?);" & _
"WHERE ([ID] = ? );"
Dim cn As ADODB.Connection, cmd As ADODB.Command
Set cn = New ADODB.Connection
cn.Open
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = sql
.CommandType = adCmdText
Dim parm(8) As ADODB.Parameter
Set parm(0) = .CreateParameter("title", adVarChar, adParamInput)
parm(0).Value = Sheets("TaskCreate").Cells(Row, 2).Value
parm(0).Size = 255
Set parm(1) = .CreateParameter("empname", adVarChar, adParamInput)
parm(1).Value = Sheets("TaskCreate").Cells(Row, 5).Value
parm(1).Size = 255
Dim i As Integer
For i = 0 To UBound(parm) - 1
.Parameters.Append parm(i)
Next
.Execute
End With
End Sub
Points to note:
- In the absence of a response to my question I've assumed you are using an ADODB connection, Excel VBA code to update an MS Access database
- The parameters must be appended in the correct order to match the "?" in the query
- I've only done the first two parameters for you
- It's better to explicitly state which property of the cell you are using rather than rely on the default i.e use
Sheets("TaskCreate").Cells(Row, 2).Value
- This wouldn't pass our in-house peer review because you have not defined which workbook
Sheets
refers to (users have a habit of clicking elsewhere while things are running!)
- instead of having the sub-query for the Engineer consider running that query first and assign the value to a parameter - it will make your final query more efficient
- instead of having Date passed as a parameter use the built in Access function for date in the raw query
- you are using a lot of "magic" numbers for your columns - consider using an enum to make it clearer what is in each column e.g.
Enum eCols
etitle = 2
empname = 5
element = 7
descrip = 8
eproduct = 9
taskcreate = 11
ID = 12
End Enum
then you can do things like
parm(0).Value = Sheets("TaskCreate").Cells(Row, eCols.etitle).Value
Edit: V2 - changed upper end of loop from hard-coded "magic" number to UBound(parm) - 1