Database with Excel VBA

[ycalsbad]

I want to update ACCESS database (name:dbsurvey.mdb) using Excel VBA
the below subroutine is working well:

Sub intro(Tender_id_num As Variant, wb As Workbook, ws As Worksheet)
Dim conn As New Connection
Dim rec As New Recordset
Dim fix3yearall As Variant
Dim fix3yearcml As Variant
Dim fix2yearother As Variant
Dim PurchaseIncentive As Variant
Dim PaymentTerm As Variant

Dim sql$, i&

' On Error GoTo end_update

fix3yearall = -CInt(ws.Cells(6, 16).Value)
fix3yearcml = -CInt(ws.Cells(7, 16).Value)
fix2yearother = -CInt(ws.Cells(8, 16).Value)
PurchaseIncentive = ws.Cells(22, 11).Value

conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
"Data Source=" + ThisWorkbook.Path + "\dbsurvey.mdb;"

sql = "update compliance" & _
" set fix_3year_all = " & "'" & fix3yearall & "'" & _
" where tender_id = " & "'" & Tender_id_num & "'"
rec.Open sql, conn

conn.Close

end_update:
End Sub


However, the program only can update One field in the table
"compliance".
If I update two or several fields, I change the SQL command to below:

sql = "update compliance" & _
" set fix_3year_cml = " & "'" & fix3yearcml & "'," & _
" set fix_3year_all = " & "'" & fix3yearall & "'" & _
" where tender_id = " & "'" & Tender_id_num & "'"

then I get a message said: ”syntax error in UPDATE statement”

I aapreciate for any help.

Thanks.

[colofnature]

You don't need the second "set" in your sql declaration, making it:

sql = "update compliance" & _
" set fix_3year_cml = " & "'" & fix3yearcml & "'," & _
" fix_3year_all = " & "'" & fix3yearall & "'" & _
" where tender_id = " & "'" & Tender_id_num & "'"


HTH
Col