missing operator error in multiple update statements

[BennyLinton]

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]

Is NBCC ID or SSN by any chance Number Data Type than Text?

 

[Uncle Gizmo]

Comment out all but one update statement and see what happens.

 

[Minty]

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 ?

 

[BennyLinton]

Both [NBCC ID] and SSN are text

 

[spikepl]

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]

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

 

[vbaInet]

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]

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).

 

[vbaInet]

Works great with Oracle.

Are you a Toad or SQL Developer fan?

 

[jdraw]

@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.

 

[vbaInet]

It's a pretty nifty feature!