missing operator error in multiple update statements (1 Viewer)

BennyLinton

Registered User.
Local time
Today, 04:31
Joined
Feb 21, 2014
Messages
263
I'm trying to run about 300 update statements and keep getting a syntax error of missing operator... what could be wrong?

UPDATE Applicants set [NBCC ID] = '351174' where SSN = '136861387'
UPDATE Applicants set [NBCC ID] = '350960' where SSN = '138885740'
UPDATE Applicants set [NBCC ID] = '350817' where SSN = '140082154'
UPDATE Applicants set [NBCC ID] = '351013' where SSN = '145766662'
 

pr2-eugin

Super Moderator
Local time
Today, 11:31
Joined
Nov 30, 2011
Messages
8,494
Is NBCC ID or SSN by any chance Number Data Type than Text?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:31
Joined
Jul 9, 2003
Messages
16,244
Comment out all but one update statement and see what happens.
 

Minty

AWF VIP
Local time
Today, 11:31
Joined
Jul 26, 2013
Messages
10,354
Is the field you are trying to update a string or numeric, and is the value you are trying to use a string or numeric ?
 

spikepl

Eledittingent Beliped
Local time
Today, 12:31
Joined
Nov 3, 2010
Messages
6,144
There is something fundamentally wrong if you have 300 hard-coded update statements as in #1.

Is this a one-off? IF not then tell us what this is about.

Are you trying to run all the update statements in one shot? Access doesn't do such things. Each is due its own Execute or whatever it is you are doing. How are you running this?
 

BennyLinton

Registered User.
Local time
Today, 04:31
Joined
Feb 21, 2014
Messages
263
this is just an isolated update from a SQL server table to an Access table
I queried the needed data from SQL Server pasted into an Excel sheet then built the update statements to paste and run in Access.... does access have a way of commenting out SQL in a query? that would make it faster.... but i can run these manually faster than I can develop a new tool.... I'm used to the ease of such in SQL Server
 
Last edited:

vbaInet

AWF VIP
Local time
Today, 11:31
Joined
Jan 22, 2010
Messages
26,374
Unfortunately, it's one UPDATE statement per query in Access like spikepl mentioned. It's not like SSMS.

Create a String array of your statements, loop through the array and execute each statement using CurrentDb.Execute()

And no you can't comment out code in a query.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:31
Joined
Jan 23, 2006
Messages
15,361
As has been said by others, this technique (multiple sql UPDATEs) doesn't work with Access. Works great with Oracle, and SSMS (according the vbaInet --I don't use SQLServer).
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:31
Joined
Jan 23, 2006
Messages
15,361
@vbaInet,
History of works great with Oracle --- years ago( 7-10) we would create txt files of UPDATE statements (from Access and other editors) to modify production Oracle records. Batching changes to PostalCode or IndustryCodes etc.

We did use Toad and Golden also.
 
Last edited:

Users who are viewing this thread

Top Bottom