Solved Update Syntax error (1 Viewer)

RickHunter84

Registered User.
Local time
Yesterday, 23:36
Joined
Dec 28, 2019
Messages
65
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:

Code:
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:

Code:
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
 

Attachments

  • Syntax error.png
    Syntax error.png
    4.4 KB · Views: 26

Gasman

Enthusiastic Amateur
Local time
Today, 04:36
Joined
Sep 21, 2011
Messages
7,906
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

Registered User.
Local time
Yesterday, 23:36
Joined
Dec 28, 2019
Messages
65
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

Enthusiastic Amateur
Local time
Today, 04:36
Joined
Sep 21, 2011
Messages
7,906
Execute and RunSql are for different purpose I believe, but I cannot remember the subtle difference. :unsure: 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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:36
Joined
Oct 29, 2018
Messages
14,620
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

Registered User.
Local time
Yesterday, 23:36
Joined
Dec 28, 2019
Messages
65
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
 

Users who are viewing this thread

Top Bottom