Re: PC UPDATEusing MS ACCESS 2002 -- PCTECH

Eva, thanks for your answer, BUT...
- As I mentioned before, I'm using WinXP and MS Office (Access)2002. I have not reach to Access 2007...
- My question was about using an "UPDATE ... WHERE EXISTS ..." as I pointed I had seen in previous posts, though for different environments.
- Trying to dig into my problem in different ways I found quite a number of references to Access/XP showing this type of problems. See, for example,

http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access

Reading further down responses to that post there are quite a number of opinions aginst Access with XP... as for its behaviour in this context.
Curious enough, some comments to other posts with same problems said it's a problem with XP, but not with Access97, nor with Access2000 !!! Strange!!!

There was also a link to MS

http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Produc

but in any case, many of those problems seemed to happen when the subSelect included some "addition calcs" like Sum(), Avg(), or the like, which were NOT in my case!

So, in the end I gave up, and decided to look for a workaround to accomplish what I needed, which I got in a very easy way, indeed, which was to do the UPDATE to an INNER JOIN, just as the one you just posted in your last answer!
It's funny we ended up with same solution, but still I'm amazed that there are so MANY different flavors of SQL which are incompatible with each other!!!

Well, thanks again to everybody!
Antonio
-----------------------------------------
Evan Harris escribió:

Hi

Access 2007 has a "feature" that prevents you doing updates until you've
clicked a button - something like UAC that's what I was asking about. It
will get in the way when trying to bulk updates and the like but not when
you are editing a table directly. When I start I get a message saying
certain content in the database is disabled.

I can only presume your SQL statement is incorrect is you are not getting
that message.

I just created an update query in access to update a column in a table from
another table. The resulting SQL as as follows:

UPDATE NewProducts INNER JOIN ProductCategory ON NewProducts.ProductCode = ProductCategory.ProductCode
SET NewProducts.[Category] = [ProductCategory].[Category];

Maybe that will help you a bit more.

I've had come challenges getting SQL syntax exactly right for Access before
- I don't know whether that is me or Access - but I do find the Query
designed helpful for generating the right SQL.

Regards
Evan Harris

-----Original Message-----
From: pctech-bounces@xxxxxxxxxxxx [mailto:pctech-bounces@xxxxxxxxxxxx] On
Behalf Of Antonio Fernandez-Vicenti
Sent: Wednesday, 27 May 2009 1:50 a.m.
To: PC Technical Discussion for iSeries Users
Subject: Re: [PCTECH] PC UPDATEusing MS ACCESS 2002

I don't quite understand your last questions...

Being signed as system Administrator, I have no other problems with the Access database.
Can open it, edit and modifiy in any way the tables: both, the original (the one that has erroneous data in some records) and the corrected records table, with the new values I would use to UPDATE the first one...
So, no problem whatsoever in editing/modifying both tables.

What do you mean by other SQL tools? In this PC there are no other "such" tools. I've used SQL in iSeries, in Delphi, etc. But what does that have to do with Access database?

If what you mean is: I could write a Delphi program, read the modifications, and for each one do the UPDATE in the original table, well, yes, probably I could, but that was out of question when I posted my original post: Does Access2002 support the type of UPDATE I would use, based on the EXIST I posted in the Subselect???