Access VBA Run-time error 3144: Syntax Error in (SQL) UPDATE statement.

I have a syntax error when I try to run my update statement (stored in a string) with the code below. It results in the SQL String (found via the debug.print line):

UPDATE [Subscriptions] SET ([Active] = false) WHERE ((([Subscriptions.ID]) = 60));

It fails on the dbs.Execute line with the error in the title.


Dim dbs As DAO.Database
    Set dbs = CurrentDb()
    
    Dim sql As String
    
    Dim SQL4Obs As String 'Prefix of Observation Number for each SQL Subscription String
    SQL4Obs = "([ID] = " & ObsIDNum & ") AND "
    
    Dim SubscriptionNums As String
    SubscriptionNums = ""
    
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("qSubscriptionsActive")
 
        sql = "UPDATE [Subscriptions] SET ([Active] = false) WHERE ((([Subscriptions.ID]) = " & rst![ID] & "));"
        Debug.Print sql
        Debug.Print ""
        dbs.Execute sql, dbFailOnError
        iCount = dbs.RecordsAffected

Open in new window

LVL 1
travisjbennettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Possibly too many parens.


UPDATE [Subscriptions] SET [Active] = false WHERE [Subscriptions.ID] = 60;

adjust your SQL string to remove the parens.

mx
travisjbennettAuthor Commented:
Nope, already tried that. Any other ideas?
jefftwilleyCommented:
if rst.eof then msgbox"'No records"

rst.movefirst
do until rst.eof
sql = "UPDATE [Subscriptions] SET [Active] = false WHERE [Subscriptions.ID] = " & rst![ID]
currentdb.execute sql, dbseechanges + dbfailonerror

rst.movenext
loop

rst.close
set rst = nothing
J
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Since there are no spaces in the names, try removing ALL brackets.

Other than that, the syntax shown is correct ... example:

UPDATE Table1 SET Table1.MyYesNo = False WHERE Table1.ID=60;

mx

travisjbennettAuthor Commented:
I'm using that code already elsewhere in the app. I have the output SQL string posted. It's only running that string (which has good info in it in general) that is a problem. What is wrong with the string (not in the code box, but in the problem.)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this:

        sql = "UPDATE [Subscriptions] SET [Active] = false WHERE Subscriptions.ID = " & rst![ID]


mx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
travisjbennettAuthor Commented:
Ok DMX: I'll give that a shot

jefftwilleyCommented:
Don't really know why you need the recordset part though

 
sql = "UPDATE [Subscriptions] SET [Active] = false FROM  qSubscriptionsActive INNER JOIN Subscriptions on [Subscriptions.ID] = qSubscriptionsActive.[ID]"

Currentdb.execute sql, dbseechanges + dbfailonerror

J
jefftwilleyCommented:
oy yea joe..I missed that didn't i :)
J
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
<ot> Jeff ... some dude from Corpus came into the blues jam I was at last night.  I met this guy when I lived in Rockport - 95-98.  Small world </ot>

mx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.