VBA Update Statement - Missing Operator in Query Expression

0
votes

First, I know that this question has been asked on this forum already, but each previous question differs from my situation and the solutions don't work. My commas are all there and I'm using a very simple query. Moving right along:

I'm using a linked table in Access, which links to a SQL Server database. Everything works except this query. If I run the text directly in SQL Server, it runs fine. Therefore, the syntax of the query must be fine.

Here's what I'm running:

CurrentDb.Execute "Update dbo_TS_Activity Set [Remarks] = ''Updated Remarks''   Where [id] = 1124 AND [Emp_Name] = ''CONFUSED''  AND [Approved] = 0"

I get Run-time error '3075' Syntax error (missing operator) in query expression ''Updated Remarks''

What I've tried:

  • Single quotes
  • Double quotes
  • Double single (two apostrophes) quotes
  • No quotes
  • Opening the linked table and manually editing it (it works)
  • Crying (just kidding)
2
sqlms-accessvbasyntax-error
Your context is a little unclear. Are Updated Remarks and CONFUSED literal text, or are they variables you're trying to concatenate into the SQL? – Ken White
Those are just strings I'm trying to insert. "Updated Remarks" is the string that is being updated. "Confused" is the employee name that is being updated. – Flat Cat

2 Answers

0
votes

This should work fine, AFAICT, from what you've posted along with your comment above.

CurrentDb.Execute "Update dbo_TS_Activity Set [Remarks] = 'Updated Remarks'   Where [id] = 1124 AND [Emp_Name] = 'CONFUSED'  AND [Approved] = 0"
0
votes

2 Things finally fixed it... I ran the update in the query designer and it gave me this weird syntax of parenthesis around the WHERE statement so it looked like:

Where (((dbo_vw_TS_Activity.[id]) = 1124))

Finally, it wanted double-double-quotes for variables, like:

SET [Remarks] = ""The updated remarks...""

Suddenly, it's happy. Consequently, I am too.