Docmd.RunSql Run-time error '3464' - update query

Question

I am trying to update access table using simple VBA code, however it finished with an error. I have tried various ways to solve it but without success. Could you please help? The code is as follow:

strSQL = "UPDATE Projects " & _
          "SET Projects.id_status = '" & Me.T_project_s.Value & "' " & _
          "WHERE Projects.id_project = '" & Me.curr_open.Value & "';"

I have also tried:

strSQL = "UPDATE Projects " & _
          "SET Projects.id_status = [" & Me.T_project_s.Value & "] " & _
          "WHERE Projects.id_project = [" & Me.curr_open.Value & "];"

or

strSQL = "UPDATE [Projects] " & _
          "SET [Projects].[id_status] = '" & Me.T_project_s.Value & "' " & _
          "WHERE [Projects].[id_project] = '" & Me.curr_open.Value & "';"

But it asks for a data which is available in those fields.

Your suggestion helped. I started with only a text then I have changed particular variables I wanted to be read. So in the Where statement there is no need to have beside "" also '' :).

strSQL = "UPDATE [Projects] " & _
          "SET [Projects].[id_status] = '" & Me.T_project_s.Value & "' " & _
          "WHERE [Projects].[id_project] = " & Me.curr_open.Value & ";"

Thanks.

[How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) — Andre, Oct 09 '17 at 10:03
Does your sql work when you replace your variables with real values? Example: `strSQL = "UPDATE Projects SET Projects.id_status = '3' WHERE Projects.id_project = '1';" ` Furthermore: Your fieldnames imply that you're using id's (often numbers) while you're sql is updating strings (with quotes). — Calaris, Oct 09 '17 at 10:12
So is your question solved? If yes, please post an answer or close the question. — Andre, Oct 09 '17 at 13:14

Parfait · Accepted Answer · 2017-10-09T21:48:55.333

Once again, here is an example where parameterization (an industry best practice in SQL programming) helps beyond avoiding SQL injection. With querydef parameters you:

avoid the need of quote enclosure;
avoid string interpolation of variables;
abstract data (i.e., VBA variables) from code (i.e., SQL statement) for cleaner scripts;
(plus as OP found out with mixed types) explicitly define the data types of values to be binded;
execute the query via DAO for smoother user interface than DoCmd.RunSQL that raises warnings to users.

Temp Query

Dim qdef As QueryDef

' PREPARED STATEMENT, DEFINING PLACEHOLDERS (NO DATA)
strSQL = "PARAMETERS [project_s_param] Text(255), [curr_open_param] Long;" & _
         " UPDATE [Projects]" & _
         " SET [Projects].[id_status] = [project_s_param]" & _
         " WHERE [Projects].[id_project] = [curr_open_param];" 

' CREATE UNNAMED TEMP QUERYDEF, ASSIGNING PREPARED STATEMENT
Set qdef = CurrentDb.CreateQueryDef("", strSQL)

' BIND VBA VALUES TO PARAMETER PLACEHOLDERS
qdef![project_s_param] = Me.T_project_s.Value
qdef![curr_open_param] = Me.curr_open.Value

' EXECUTE ACTION
qdef.Execute dbFailOnError

Set qdef = Nothing

Saved Query

Even better, save entire prepared statement as a stored Access query and avoid any SQL in VBA.

SQL (save as any regular query object whose name is referenced in VBA)

PARAMETERS [project_s_param] Text(255), [curr_open_param] Long;
UPDATE [Projects]
SET [Projects].[id_status] = [project_s_param]
WHERE [Projects].[id_project] = [curr_open_param]

VBA

Dim qdef As QueryDef

' REFERENCE EXISTING QUERYDEF, ASSIGNING PREPARED STATEMENT
Set qdef = CurrentDb.QueryDefs("mySavedQuery")

' BIND VBA VALUES TO PARAMETER PLACEHOLDERS
qdef![project_s_param] = Me.T_project_s.Value
qdef![curr_open_param] = Me.curr_open.Value

' EXECUTE ACTION
qdef.Execute dbFailOnError

Set qdef = Nothing

Docmd.RunSql Run-time error '3464' - update query

1 Answers1