Am I exepcting too Much

  • Thread starter Thread starter Kevin W
  • Start date Start date
K

Kevin W

Hi,

Can anybody spot my silly mistake ?

I'm having problems running a query which attempts to
update fields in a Excel 97 spreadsheet, linked as a table
(Clarifications) into an Access 97 database using two
native tables, tblIssues and
tblClar_Status_to_Issue_Status. I'm using the following
query :

UPDATE Clarifications INNER JOIN tblIssues ON
Clarifications.[Issue No] = tblIssues.Issue_ID SET
Clarifications.[CLAR STATUS] = (SELECT
Clarification_Status FROM tblClar_Status_to_Issue_Status
WHERE
tblIssues.Status =
tblClar_Status_to_Issue_Status.Issue_Status);

The query 'compiles' without any errors but when I try to
open it, it threatens to do something and then I get a
message :

"Operation must use an updateable query".

I presume this is saying that Access is treating the
linked table as a view query and that this is not
updateable. However, If I open the Clarifications
datasheet, I can alter fields and the changes are
reflected in the underlying spreadsheet which is what I
want to achieve. The Updateable property (read-only)for
the Clarifications tabledef object is indeed false so this
is consistent with Access's response to my query but
doesn't explain why I can update the spreasheed
mandrollically via the datasheet for the linked table. The
spreadsheet is not read-only. The Microsoft website hints
at problems in this area with solutions when encoutering
this problem in ADO access to external excel spreadsheets
but seems quiet on this particular manifestation of the
problem.

Any help would be appreciated, particularly since
Microsoft have just recently stopped supporting this
product. If it's a known problem, that's fine because I
can stop bothering.

Thanks in advance,

Kevin
 
It is saying that the Join:
Clarifications INNER JOIN tblIssues ON Clarifications.[Issue No] =
tblIssues.Issue_ID
is not updateable. This may be because [Issue No] in Clarifications does
not have a unique index. (This is likely if the table is a linked
spreadsheet). To test, try creating a SELECT query with that join and see
if it is updateable (ie that there is a NewRecord at the bottom of the
datasheet). This means that although both tables are updateable by
themselves, the Join of them is not.

However, I am missing something. Since you are only updating a field in
Clarifications, why do you even need the Join? See if you can do it
without.
 
Hi,

thanks for your reply.

I did indeed have records in the Clarifications
spreadsheet in which Clarifications.[Issue No] was
repeated. Unfortunately, removing the duplicate records in
Clarifications did not removed the error message.

What I'm trying to achieve is to update the Clarifications.
[CLAR STATUS] field based on the value of the
tblIssues.Status field, via a lookup in the
tblClar_Status_to_Issue_Status table, for those tblIssues
and Clarifications records for which tblIssues.Issue_ID =
Clarifications.[Issue No]. I thought that a join would be
the easiest way of achieving this. Do you have any other
ideas or can you spot any errors in my query, given my
stated aim ?

Thanks in advance,

Kevin
-----Original Message-----
It is saying that the Join:
Clarifications INNER JOIN tblIssues ON Clarifications. [Issue No] =
tblIssues.Issue_ID
is not updateable. This may be because [Issue No] in Clarifications does
not have a unique index. (This is likely if the table is a linked
spreadsheet). To test, try creating a SELECT query with that join and see
if it is updateable (ie that there is a NewRecord at the bottom of the
datasheet). This means that although both tables are updateable by
themselves, the Join of them is not.

However, I am missing something. Since you are only updating a field in
Clarifications, why do you even need the Join? See if you can do it
without.
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


Hi,

Can anybody spot my silly mistake ?

I'm having problems running a query which attempts to
update fields in a Excel 97 spreadsheet, linked as a table
(Clarifications) into an Access 97 database using two
native tables, tblIssues and
tblClar_Status_to_Issue_Status. I'm using the following
query :

UPDATE Clarifications INNER JOIN tblIssues ON
Clarifications.[Issue No] = tblIssues.Issue_ID SET
Clarifications.[CLAR STATUS] = (SELECT
Clarification_Status FROM tblClar_Status_to_Issue_Status
WHERE
tblIssues.Status =
tblClar_Status_to_Issue_Status.Issue_Status);

The query 'compiles' without any errors but when I try to
open it, it threatens to do something and then I get a
message :

"Operation must use an updateable query".

I presume this is saying that Access is treating the
linked table as a view query and that this is not
updateable. However, If I open the Clarifications
datasheet, I can alter fields and the changes are
reflected in the underlying spreadsheet which is what I
want to achieve. The Updateable property (read-only)for
the Clarifications tabledef object is indeed false so this
is consistent with Access's response to my query but
doesn't explain why I can update the spreasheed
mandrollically via the datasheet for the linked table. The
spreadsheet is not read-only. The Microsoft website hints
at problems in this area with solutions when encoutering
this problem in ADO access to external excel spreadsheets
but seems quiet on this particular manifestation of the
problem.

Any help would be appreciated, particularly since
Microsoft have just recently stopped supporting this
product. If it's a known problem, that's fine because I
can stop bothering.

Thanks in advance,

Kevin
Click to expand...


.
Click to expand...
 
Back
Top