Update query..

[Vincent Jones]

I must be a dork!!!
I can't figure this out.
strQuery2 = "Update tbCard_Temp Set tbCard_Temp.Id='tbCardSwipe.Id
From tbCardSwipe'"


and i get this message
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression 'tbCardSwipe.Id From tbCardSwipe'.

 

[Ray at]

What are you trying to do? Your current query there is trying to set an ID
to be this literal string: 'tbCardSwipe.Id From tbCardSwipe'

Is that what you want? Or do you want something more like:

Update tbCard_Temp SET ID=(SELECT TOP 1 [ID] FROM tbCardSwipe WHERE
[something]='something') WHERE [something]='something'

Ray at work

 

[Aaron Bertrand - MVP]

Maybe you meant:

UPDATE tbCard_Temp SET Id =
(SELECT tbCardSwipe.Id FROM tbCardSwipe)

However, that would set *every* row in tbCard_Temp to some arbitrary id from
tbCardSwipe. (Unless tbCardSwipe has exactly one row.)

Sounds more like you meant to perform a join between the table to be updated
and the table from which to derive the value. I'm not sure if Access
supports updating from a join... in SQL Server, the syntax would be:

UPDATE T
SET T.Id = s.Id
FROM tbCard_Temp T
INNER JOIN tbCardSwipe s
ON T.primary_key_col = s.primary_key_col