Access 97 and subqueries

J

Jeff

I'm trying to use a subquery in an Update statement a la:

UPDATE tblRecipes
SET Field1 = (SELECT Field1
FROM tblRecipesBackupTable
WHERE ID = 3)
WHERE ID = 3'

What I'm trying to do is build a way to initiate a backup
plan from a 'system default' in case a user screws up a
recipe and needs to get the original one back. To do this
reliably (there are 2600 recipes in the DB) I'd need to
use a subquery to find the appropriate backed up recipe
based on ID number.

Access 97 gives me the error: Operation must use an
updatable query.

Can anyone let me know how to use subqueries correctly in
Access 97 Update SQL statements?

Thanks,
JEff
 
M

[MVP] S.Clark

Access is real fickle about Update and Delete queries. Sometimes you just
have to take a few extra steps to perform what you think would be simple.
Try an Inner Join instead, make sure both tables are keyed, etc.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
J

John Spencer (MVP)

Two ways that come to mind.

UPDATE tblRecipes INNER JOIN tblRecipesBackupTable
On TblRecipes.ID = TblRecipesBackupTable.id
SET tblRecipes.Field1 = tblRecipesBackupTable.field1
WHERE tblRecipes.ID = 3

OR you can use the DLOOKUP function

UPDATE tblRecipes
SET tblRecipes.Field1 = DLOOKUP("Field1","tblRecipesBackupTable","ID=3")
WHERE tblRecipes.ID = 3
 
Top