Solved Update Syntax error

[RickHunter84]

Dear friends,

I hope you are doing well.

I've been running into a syntax issue when executing a update statement on VBA.

The line in discussion:

CurrentDb.Execute "UPDATE tblPurchaseOrderDetail SET lineStatus_FK = 2" & "WHERE [IncomingID]=" & IdLine & ";"

lineStatus_FK is number (connected status tbl)
IncomingID is number (Autonumber)
IdLine is number Dim as Integer

It looks like a straight forward update line, but I continue getting the error in the picture (see attached), i have tried different syntax and the error persists.

Also, separate question, i have seen a lot of people coding the update statement in different ways where the code is added into a variable, for example:

Dim db As DAO.Database
Dim rs As Recordset
Dim sSQL As String
Dim sSQL1 As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("T05_Pr2_Null_Not_In_Rem")

sSQL = "UPDATE T05_Pr2_Null_Not_In_Rem SET SAP = NULL " & _
     " WHERE (SAP = 1 AND SAG = 3)"
     DoCmd.RunSQL sSQL

sSQL = "UPDATE T05_Pr2_Null_Not_In_Rem SET SAG = NULL " & _
     " WHERE (SAP = 1 AND SAG = 3)"
     DoCmd.RunSQL sSQL

rs.Close
Set rs = Nothing
db.Close

is there any difference between doing a Currentdb.Execute and the dividing the code in smaller chunks?

Looking forward to your feedback.

thank you!

Rick

 

[Gasman]

Well it tells you the error? there is no space between the 2 and WHERE?

Put the the SQL into a string, much like the have done with the other code, then you can Debug.Print it to check the syntax.

 

[RickHunter84]

Well it tells you the error? there is no space between the 2 and WHERE?

Put the the SQL into a string, much like the have done with the other code, then you can Debug.Print it to check the syntax.

lol, yep it was the space between the 2 and where. thank you Gasman.

what about the the other question? is there any difference between doing a Currentdb.Execute and the dividing the code in smaller chunks? - is it just preference, best practice?

 

[Gasman]

Execute and RunSql are for different purpose I believe, but I cannot remember the subtle difference. Have a Google for both.
However unless it is a simple hardcoded statement, I put the SQL into a string and check it with Debug.Print before trying to use it.?

You would not believe the number of times I write that statement for situations just like yours.

Certainly build it in chunks. For anything quite large I would have the SELECT/UPDATE as one part, the WHERE/HAVING as another and ORDER & GROUP by another, easier to debug again.

Edit: Here you go, this is one such link https://social.msdn.microsoft.com/F...aea8/docmdrunsql-vs-dbexecute?forum=accessdev

 

[theDBguy]

what about the the other question? is there any difference between doing a Currentdb.Execute and the dividing the code in smaller chunks? - is it just preference, best practice?

If we use your examples, the difference is you're wasting cpu cycles and using additional trip to the BE when you divide the SQL to update singular fields. You can update all the fields at the same time and be more efficient.

 

[RickHunter84]

If we use your examples, the difference is you're wasting cpu cycles and using additional trip to the BE when you divide the SQL to update singular fields. You can update all the fields at the same time and be more efficient.

Programming is about efficiencies. I'm changing my ways then.

got it, thank you!

Rick

 

[theDBguy]

Programming is about efficiencies. I'm changing my ways then.

got it, thank you!

Rick

Hi Rick. Good luck with your project.