Runtime '3129' Update Statement

Question

I'm writing in VBA/MS Access and having trouble with this error. I think it's the date. I use the function:

DOB = Format(DOB,"\#mm\/dd\/yyyy\#")

Now this works for the INSERT sql also running in my code. It looks like this when I Debug.Print

,#01/01/1900#,

My problem is with my UPDATE sql statement.

"UPDATE table SET " & "DOB=" & DOB & "," & "Last_Maintain_Date=" & LastMaintainDate & _
"," & LastmaintainUser & " WHERE ID=" & ID

Debug.Print

UPDATE table SET DOB=#03/01/1983#,Last_Maintain_Date="10/11/2019 2:59:47 PM",Last_Maintain_User="User Name" WHERE ID=11111111

DOB is a date field. ID is a numeric field, and the other two are strings. I wish run-time errors would help you a little more than just "your code is wrong".

I don't see this in your update statement. Last_Maintain_User= But it's in the Debug.Print. — SS_DBA
The message for error 3129 is "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." I don't see a problem with the syntax in the Debug.Print of your UPDATE statement. Please show us how you're attempting to execute it. — HansUp
@WEI_DBA it's there, it's just a typo, The Debug.Print is what is being sent to the RunSQL command. — dcary
@HansUp I'm storing the value in a string then doing DoCmd.RunSQL SQLstring — dcary

Parfait Parfait · Accepted Answer · 2019-10-11T19:45:01

Consider parameterization which is supported in MS Access using QueryDefs and helps avoid the need for punctuation or concatenation:

Dim strSQL As String
Dim qdef As QueryDef

' PREPARED SQL STATEMENT WITH PARAMETERS CLAUSE (NO DATA)
strSQL  = "PARAMETERS [prmDOB] Date, [prmLastMaintainDate] Date, "_
            & "       [prmLastmaintainUser] Text, [prmID] Long;"
            & " UPDATE mytable " _
            & " SET DOB = [prmDOB], " _
            & "     Last_Maintain_Date = [prmLastMaintainDate], " _
            & "     LastmaintainUser = [prmLastmaintainUser] " _
            & " WHERE ID = [prmID]"

Set qdef = CurrentDb.QueryDefs("", strSQL)

' BIND PARAMS (NO QUOTES OR HASHTAGS)
qdef![prmDOB] = DOB                            ' ASSUMED TO BE A DATE TYPE
qdef![prmLastMaintainDate] = LastMaintainDate  ' ASSUMED TO BE A DATE TYPE 
qdef![prmLastmaintainUser] = LastmaintainUser  ' ASSUMED TO BE A STRING TYPE
qdef![prmID] = ID                              ' ASSUMED TO BE AN INT/LONG TYPE

' RUN ACTION
qdef.Execute dbFailOnError

Set qdef = Nothing

You can even save above update statement as an MS Access stored query and reference it below, entirely divorcing SQL from VBA:

Set qdef = CurrentDb.QueryDefs("mySavedUpdateQuery")

' BIND PARAMS
qdef![prmDOB] = DOB
qdef![prmLastMaintainDate] = LastMaintainDate
qdef![prmLastmaintainUser] = LastmaintainUser
qdef![prmID] = ID

' RUN ACTION    
qdef.Execute dbFailOnError

Set qdef = Nothing

Please note SQL parameterization is a programming industry best practice that extends beyond VBA to any application layer that runs SQL as a lower-level language including general purpose languages like Java, Python, C#, PHP, and others. Though implementation differs, concept is the same to separate SQL from application code. See StackOverflow Co-Founder Jeff Atwood's Give me parameterized SQL, or give me death.

Runtime '3129' Update Statement

3 Answers