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

travisjbennett
travisjbennett used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Possibly too many parens.


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

adjust your SQL string to remove the parens.

mx

Author

Commented:
Nope, already tried that. Any other ideas?
Top Expert 2006

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

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

Author

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.)
Database Architect / Application Developer
Top Expert 2007
Commented:
Try this:

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


mx

Author

Commented:
Ok DMX: I'll give that a shot

Top Expert 2006

Commented:
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
Top Expert 2006

Commented:
oy yea joe..I missed that didn't i :)
J
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial