By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

Updating data in a table

P: n/a
Hi All,

In AccessXP I create an Access table then create an update query which
updates a field in that table with data from an SQLServer table and
when I run it I get the message

'operation must use an updateable query'

here is the sql of the query

UPDATE tblFeatureCounts1 INNER JOIN dbo_tblRPT_CLM_SUBRO ON
tblFeatureCounts1.clm_ofcnum = dbo_tblRPT_CLM_SUBRO.clm_ofcnum SET
tblFeatureCounts1.adjr_id = dbo_tblRPT_CLM_SUBRO!adjr_id,
tblFeatureCounts1.subr_id = dbo_tblRPT_CLM_SUBRO!subr_id,
tblFeatureCounts1.rpt_ofc = dbo_tblRPT_CLM_SUBRO!rpt_ofc;

I don't understand what the issue is with this, what's wrong????
thanks
bobh.

Jan 16 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
The message suggests that you are trying to update a table that is
joined to another table - perhaps violating referential integrity.
Check your relationships window to see what kind of referential
integrity rule is in place. If there is a relationship between the
table you want to update and some other table, you need to figure out
what the nature of that relationship is and see if your update query is
violating any part of that relationship - usually a primary key
violation - which means that you would need to exclude rows from the
source data that violate your rules.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jan 16 '07 #2

P: n/a
I have nothing setup in relationships and I looked to be sure. All I'm
doing is updating fields in a jet table with data from a sql table.
bobh.

On Jan 16, 11:05 am, Rich P <rpng...@aol.comwrote:
The message suggests that you are trying to update a table that is
joined to another table - perhaps violating referential integrity.
Check your relationships window to see what kind of referential
integrity rule is in place. If there is a relationship between the
table you want to update and some other table, you need to figure out
what the nature of that relationship is and see if your update query is
violating any part of that relationship - usually a primary key
violation - which means that you would need to exclude rows from the
source data that violate your rules.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Jan 16 '07 #3

P: n/a
Is the table you are trying to update joined to any other tables? If
so, then that is causing the problem. How about this: are you trying
to update a table or a query? I meant to ask that before. I will guess
you are probably trying to update a query which consists of joined
tables. If this is the case, then you need to update only the one table
and not the query. A query is a form of a relationship - well joined
tables.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jan 16 '07 #4

P: n/a

bobh wrote:
Hi All,

In AccessXP I create an Access table then create an update query which
updates a field in that table with data from an SQLServer table and
when I run it I get the message

'operation must use an updateable query'

here is the sql of the query

UPDATE tblFeatureCounts1 INNER JOIN dbo_tblRPT_CLM_SUBRO ON
tblFeatureCounts1.clm_ofcnum = dbo_tblRPT_CLM_SUBRO.clm_ofcnum SET
tblFeatureCounts1.adjr_id = dbo_tblRPT_CLM_SUBRO!adjr_id,
tblFeatureCounts1.subr_id = dbo_tblRPT_CLM_SUBRO!subr_id,
tblFeatureCounts1.rpt_ofc = dbo_tblRPT_CLM_SUBRO!rpt_ofc;

I don't understand what the issue is with this, what's wrong????
I got bitten by the exact same problem yesterday, except that my linked
table was on Oracle instead of MS_SQL. When I created the application
my UPDATE query worked, but then something got changed on the Oracle
server and the query started failing with 'operation must use an
updateable query'.

My workaround was to dump the external data into a temporary table and
then use it to perform the UPDATE. In your case, something like...
SELECT dbo_tblRPT_CLM_SUBRO.* INTO zzz_temp_tbl
FROM dbo_tblRPT_CLM_SUBRO
WHERE dbo_tblRPT_CLM_SUBRO.clm_ofcnum IN
(SELECT DISTINCT tblFeatureCounts1.clm_ofcnum
FROM tblFeatureCounts1);

UPDATE tblFeatureCounts1 INNER JOIN zzz_temp_tbl ON
tblFeatureCounts1.clm_ofcnum = zzz_temp_tbl.clm_ofcnum SET
tblFeatureCounts1.adjr_id = zzz_temp_tbl!adjr_id,
tblFeatureCounts1.subr_id = zzz_temp_tbl!subr_id,
tblFeatureCounts1.rpt_ofc = zzz_temp_tbl!rpt_ofc;
....though perhaps someone else may have a more direct solution.

Jan 16 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.