Update query not updateable

[vba317]

I am getting this error from an update query I just created. Could someone tell what causes this error? Any help with this matter is greatly appreciated. The table I am getting the information from (dbo_rpt_dat_ARDetail) is a linked table the table I am trying to update (calc_Compare) is an access table. Tom

UPDATE dbo_rpt_dat_ARDetail INNER JOIN calc_Compare ON (dbo_rpt_dat_ARDetail.slid = calc_Compare.SLID) AND (dbo_rpt_dat_ARDetail.eid = calc_Compare.EncID) AND (dbo_rpt_dat_ARDetail.aid = calc_Compare.AID) SET calc_Compare.AcctName = [dbo_rpt_dat_ARDetail].[PatName]
WHERE (((calc_Compare.AID)=267658));

 

[PHV]

What are the PrimaryKeys of dbo_rpt_dat_ARDetail and calc_Compare ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Neither of the tables have a primary key.

 

[Andrzejek]

Looks a little backwards:

[pre]
UPDATE dbo_rpt_dat_ARDetail
INNER JOIN calc_Compare
ON (dbo_rpt_dat_ARDetail.slid = calc_Compare.SLID)
AND (dbo_rpt_dat_ARDetail.eid = calc_Compare.EncID)
AND (dbo_rpt_dat_ARDetail.aid = calc_Compare.AID)
SET [blue]calc_Compare.AcctName = [dbo_rpt_dat_ARDetail].[PatName][/blue]
WHERE (((calc_Compare.AID)=267658));
[/pre]

You want to UPDATE [tt]dbo_rpt_dat_ARDetail[/tt] table, but you
SET a field in [tt]calc_Compare[/tt] table to some value.


Have fun.

---- Andy

 

[vba317]

Andy,
I want to update the calc_Compare table with info from the dbo_rpt_dat_ARDetail table.

 

[PHV]

Neither of the tables have a primary key
So, the query is NOT updatable.
You may try this:

UPDATE calc_Compare SET AcctName (
SELECT PatName FROM dbo_rpt_dat_ARDetail
 WHERE slid = calc_Compare.SLID
   AND eid  = calc_Compare.EncID
   AND aid  = calc_Compare.AID
) 
WHERE AID=267658

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV,
When I enter your code I get an arror : Syntax error in the update statement

 

[dhookom]

IMO, it's a mistake to not have a primary key field or fields on every table.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Sorry for the typo:

UPDATE calc_Compare SET AcctName [!]=[/!] (
SELECT PatName FROM dbo_rpt_dat_ARDetail
 WHERE slid = calc_Compare.SLID
   AND eid  = calc_Compare.EncID
   AND aid  = calc_Compare.AID
) 
WHERE AID=267658

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Sorry I still get the query not updateable error

 

[dhookom]

Can you edit data directly in the tables?

Did you create any primary keys?

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

Not ideal, but this may suit:

UPDATE calc_Compare SET AcctName = DLookUp("PatName","dbo_rpt_dat_ARDetail","slid = " & calc_Compare.SLID & " AND eid  = " & calc_Compare.EncID & " AND aid  = " & calc_Compare.AID) 
WHERE AID=267658

Assuming that all fields are numeric.

http://lessthandot.com

 

[vba317]

dhookom,

You are correct every table should have a primary key. I didn't work for the company when the database was designed. The designer left about 6 months ago. So in this case would I have to add the primary key to both tables? If so would I have to make an autocount field?

 

[dhookom]

From your SQL, I would expect dbo_rpt_dat_ARDetail to have a unique index on slid, eid, and aid.

Duane
Hook'D on Access
MS Access MVP