Access 97 and subqueries

  • Thread starter Thread starter Jeff
  • Start date Start date
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
 
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
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
 
Back
Top