Re: PC UPDATEusing MS ACCESS 2002

[Antonio Fernandez-Vicenti <afvaiv@xxxxxxxxxxxxxx>]

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???
>
>
>