how to UPDATE LINKED tables? - security

[Guest]

Friday, August 12, 2005 5:46 PM
--------------------------------------------------------

I have never been able to run a simple UPDATE query in my Access setup.
Here is what I get:

update TINWSF
set name = 'cliff-noronha--2005-08-09'
where Ltrim(rtrim(TINWSF.name)) is null

ERROR MESSAGE: OPERATION MUST USE AN UPDATEABLE QUERY

From reading a few posts, I have gathered that the culprit is the fact that
all my tables (including TINWSF) are actually linked to Oracle tables.

Which explains why it works in SQL+, which is not allowed where I work. Is
there ANY way that I can accomplish an UPDATE to a LINKED table in Access?

Please help. Thanks!

-- Cliff

-------------------------------------------------------------------------------

 

[TC]

Not to answer your question, but:

Your where clause does not make sense. The only time that your where
expression will be True, is when the 'name' field is NULL; in which
case, the ltrim/rtrim is not achieving anything, and you could replace
the whole with: were [name] is null. (The brackets may be necessary
because 'name' is a reserved word in Access.)

If the 'name' field can be /blanks/, and you want to test for that, the
test that you have written will not work at all. The ltrim/rtim will
return a zero-length or empty string, "", but an empty string is NOT
null, so the test will fail.

To test for both, ie. null or all-blanks, use this:

where [name] is null or trim$([name])=""

or this:

where trim$(nz([name],"")) = ""

HTH,
TC