Operation must use updateable query problem

[MKuria]

SQL I am using -
UPDATE DISTINCTROW [Hard Copy] LEFT JOIN [Append to HardCopy 1] ON [Hard
Copy].CustID = [Append to HardCopy 1].CustID SET [Append to HardCopy
1].[Response Due Date] = [Hard Copy]![Response Due Date], [Append to HardCopy
1].[Service Start Date] = [Hard Copy]![Service Start Date], [Append to
HardCopy 1].[Customer ID] = [Hard Copy]![Service Completion Date]
WITH OWNERACCESS OPTION;
I am running the SQL above and I get an error operation must use updateable
query.
CustID is the primary key on the tbl HARD COPY. On the Append to HardCopy1
(this is a query) CustID is concatenated. Is this what the issue is?

 

[Golfinray]

Is the query you are trying to update updateable? Is the arrow and asterisk
at the bottom highlighted or grayed out? If it is grayed out, the query is
not updateable.

 

[MKuria]

Thanks for your quick response - HEre is what I am attempting
I am trying to Update the Hard copy table using the data from the Append to
HardCopy1 query. Hard Copy is master table, then I import new data and store
it in temp table - run a query to create custid field and then I want to run
an update query to update the 3 fields in Hard Copy table.
I create a select query and it runs - issue is when I change it to update
query.
--
mmk

Is the query you are trying to update updateable? Is the arrow and asterisk
at the bottom highlighted or grayed out? If it is grayed out, the query is
not updateable.

SQL I am using -
UPDATE DISTINCTROW [Hard Copy] LEFT JOIN [Append to HardCopy 1] ON [Hard
Copy].CustID = [Append to HardCopy 1].CustID SET [Append to HardCopy
1].[Response Due Date] = [Hard Copy]![Response Due Date], [Append to HardCopy
1].[Service Start Date] = [Hard Copy]![Service Start Date], [Append to
HardCopy 1].[Customer ID] = [Hard Copy]![Service Completion Date]
WITH OWNERACCESS OPTION;
I am running the SQL above and I get an error operation must use updateable
query.
CustID is the primary key on the tbl HARD COPY. On the Append to HardCopy1
(this is a query) CustID is concatenated. Is this what the issue is?

 

[AFSSkier]

I'm having a similar issue. The first query works, but when I add a source
table I get "Operation must use an updatable query. (Error 3073)". Also,
where is the arrow and asterisk at the bottom, bottom of where?

UPDATE scandata
SET scandata.Description = "test";

2nd query
UPDATE scandata
INNER JOIN DIV1
ON scandata.UPC = DIV1.UPC
SET scandata.Description = DIV1.Description;

--
Thanks, Kevin

Is the query you are trying to update updateable? Is the arrow and asterisk
at the bottom highlighted or grayed out? If it is grayed out, the query is
not updateable.

SQL I am using -
UPDATE DISTINCTROW [Hard Copy] LEFT JOIN [Append to HardCopy 1] ON [Hard
Copy].CustID = [Append to HardCopy 1].CustID SET [Append to HardCopy
1].[Response Due Date] = [Hard Copy]![Response Due Date], [Append to HardCopy
1].[Service Start Date] = [Hard Copy]![Service Start Date], [Append to
HardCopy 1].[Customer ID] = [Hard Copy]![Service Completion Date]
WITH OWNERACCESS OPTION;
I am running the SQL above and I get an error operation must use updateable
query.
CustID is the primary key on the tbl HARD COPY. On the Append to HardCopy1
(this is a query) CustID is concatenated. Is this what the issue is?

 

[John W. Vinson]

I want to run
an update query to update the 3 fields in Hard Copy table.

Do you want to *UPDATE* existing records, or *APPEND NEW* records?

 

[Stefan Hoffmann]

hi,

SQL I am using -
UPDATE DISTINCTROW [Hard Copy] LEFT JOIN [Append to HardCopy 1] ON [Hard
Copy].CustID = [Append to HardCopy 1].CustID SET [Append to HardCopy
1].[Response Due Date] = [Hard Copy]![Response Due Date], [Append to HardCopy
1].[Service Start Date] = [Hard Copy]![Service Start Date], [Append to
HardCopy 1].[Customer ID] = [Hard Copy]![Service Completion Date]
WITH OWNERACCESS OPTION;
I am running the SQL above and I get an error operation must use updateable
query.
CustID is the primary key on the tbl HARD COPY. On the Append to HardCopy1
(this is a query) CustID is concatenated. Is this what the issue is?

http://allenbrowne.com/ser-61.html

Especially your queries must be key-preserving. So check whether your
queries contains alle the primary key fields.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I'm having a similar issue. The first query works, but when I add a source
table I get "Operation must use an updatable query. (Error 3073)". Also,
where is the arrow and asterisk at the bottom, bottom of where?

UPDATE scandata
SET scandata.Description = "test";

2nd query
UPDATE scandata
INNER JOIN DIV1
ON scandata.UPC = DIV1.UPC
SET scandata.Description = DIV1.Description;

Same link:

http://allenbrowne.com/ser-61.html

Check whether both tables have primary keys defined.


mfG
--> stefan <--

 

[AFSSkier]

I'm want to update some of the fields in an existing table with the data from
an odbc source table.

 

[AFSSkier]

I found the problem I'm having. It was my sub-query of the source, I was
joining to the final table. When I changed the source into a Make Table, it
worked fine.

I was doing a sub-query on the source to define a A13 to an A14 (like a
hold), so I could join it to a A14 in the final table. The Make Table works,
unless you know how I can define the source table's A13 to an A14 at the
Update query.
--
Thanks for everyone help!

hi,

SQL I am using -
UPDATE DISTINCTROW [Hard Copy] LEFT JOIN [Append to HardCopy 1] ON [Hard
Copy].CustID = [Append to HardCopy 1].CustID SET [Append to HardCopy
1].[Response Due Date] = [Hard Copy]![Response Due Date], [Append to HardCopy
1].[Service Start Date] = [Hard Copy]![Service Start Date], [Append to
HardCopy 1].[Customer ID] = [Hard Copy]![Service Completion Date]
WITH OWNERACCESS OPTION;
I am running the SQL above and I get an error operation must use updateable
query.
CustID is the primary key on the tbl HARD COPY. On the Append to HardCopy1
(this is a query) CustID is concatenated. Is this what the issue is?

http://allenbrowne.com/ser-61.html

Especially your queries must be key-preserving. So check whether your
queries contains alle the primary key fields.

mfG
--> stefan <--

 

[John W. Vinson]

UPDATE DISTINCTROW [Hard Copy] LEFT JOIN [Append to HardCopy 1] ON [Hard
Copy].CustID = [Append to HardCopy 1].CustID SET [Append to HardCopy
1].[Response Due Date] = [Hard Copy]![Response Due Date], [Append to HardCopy
1].[Service Start Date] = [Hard Copy]![Service Start Date], [Append to
HardCopy 1].[Customer ID] = [Hard Copy]![Service Completion Date]
WITH OWNERACCESS OPTION;
I am running the SQL above and I get an error operation must use updateable
query.
CustID is the primary key on the tbl HARD COPY. On the Append to HardCopy1
(this is a query) CustID is concatenated. Is this what the issue is?

What do you mean by "concatenated"? [Append to Hard Copy 1] will not be
updateable unless Access can uniquely identify which record to update - i.e.
by its Primary Key.