Update Access Record via VBA

[aespinoza]

Hi Guys

I am needing to Update an Access record via VBA in Excel

I have the following SQL written but i keep getting "Syntax error in UPDATE statement?
it might look complicated i am sorry but i have been going over this for week, and have written in several permutations but i cant for the life of me find where the error may be

sQRY1 = "UPDATE Main_Table SET Date = " & Cells(2, 2).Value & ", Time = " & Cells(2, 3).Value & "," & _"QA = " & Cells(2, 4).Value & ", Department = " & Cells(2, 5).Value & ", Workstream = " & Cells(2, 6).Value & ", Type_of_Intercation = " & Cells(2, 7).Value & "," & _
"Specialist = " & Cells(2, 8).Value & ", Team_Leader = " & Cells(2, 9).Value & ", Purpose = " & Cells(2, 10).Value & ", Coaching_Type = " & Cells(2, 11).Value & "," & _
"Time_Taken = " & Cells(2, 12).Value & ", Q5 = " & Cells(2, 13).Value & "," & _
"Q6 = " & Cells(2, 14).Value & ", Q7 = " & Cells(2, 15).Value & ", Q8 = " & Cells(2, 16).Value & ", Q9 = " & Cells(2, 17).Value & ", Q10 = " & Cells(2, 18).Value & "," & _
"Q11 = " & Cells(2, 19).Value & ", Q12 = " & Cells(2, 20).Value & ", Q13 = " & Cells(2, 21).Value & ", Q14 = " & Cells(2, 22).Value & ", Q15 = " & Cells(2, 23).Value & "," & _
"Q16 = " & Cells(2, 24).Value & ", Q17 = " & Cells(2, 25).Value & ", Q18 = " & Cells(2, 26).Value & ", Q19 = " & Cells(2, 27).Value & ", Q20 = " & Cells(2, 28).Value & "," & _
"Q21 = " & Cells(2, 29).Value & ", Q22 = " & Cells(2, 30).Value & ", Q23 = " & Cells(2, 31).Value & ", Q24 = " & Cells(2, 32).Value & ", Q25 = " & Cells(2, 33).Value & "," & _
"Q26 = " & Cells(2, 34).Value & ", Q27 = " & Cells(2, 35).Value & ", Q28 = " & Cells(2, 36).Value & ", Q29 = " & Cells(2, 37).Value & "," & _
"New_Objectives = " & Cells(2, 38).Value & ", Comments = '" & Cells(2, 39).Value & "' WHERE Survey_ID = '" & Range("A2").Value & "'"

Any help would be appreciated

Thanks

 

[wideboydixon]

Put this line below that one:

Debug.Print sQRY1

That will print out the created query to the debug window and you can inspect it there to allow easier diagnosis of the problem. You could then try copying the query and running it in Access to see if you get the same error there.

WBD

 

[aespinoza]

Thanks Wideboy

I was using MsgBox sQRY1 to read the better.
deBug enabled me to copy and paste into Access and run and i still get a Syntax error. but there the word "Date" is Highlighted, any ideas. below is the full statement. do i need to surround a date with # or something?

UPDATE Main_Table SET Date = 13/04/2018, Time = 0.402534722222222,QA = Bradley Ireland, Department = Optics, Workstream = Cx - Lensmail, Type_of_Intercation = Call,Specialist = Susan Glass, Team_Leader = Nick Cook, Purpose = Monthly, Coaching_Type = Remote Listening,Time_Taken = 0.0625, Q5 = Yes,Q6 = Continue, Q7 = Continue, Q8 = Continue, Q9 = Continue, Q10 = Continue,Q11 = Continue, Q12 = 0, Q13 = 0, Q14 = 0, Q15 = Continue,Q16 = Continue, Q17 = 0, Q18 = 0, Q19 = 0, Q20 = 0,Q21 = 0, Q22 = 0, Q23 = 0, Q24 = 0, Q25 = 0,Q26 = 0, Q27 = 0, Q28 = 0, Q29 = Yes,New_Objectives = Maintain Service Excellence, Comments = 'On all 4 calls, Sue completed full DPA, checked contact information and gave clear and accurate advice. 4 great calls as per her usual standards and would like to see Sue maintain this going forward. ' WHERE Survey_ID = '1389'

Thanks

 

[aespinoza]

I got it Working!

had to makes to every text value had " ' " either side

Thanks for your help literally only worked it out from the Debug.print

Thanks

 

[wideboydixon]

Glad you got it sorted.