Operation must be updateable query error

[vba317]

I am getting the Operation must be updateable query error on a query that I wrote. I have tried amendeing the query many times with no luck. Neither table has any primary keys. The database is not in read only mode. The table I am getting the info from is a linked table from a sql server.
Any help is appreciated.

Tom

UPDATE dbo_rpt_dat_ARDetail
 INNER JOIN PROC_RptSrc_ARDetail ON (dbo_rpt_dat_ARDetail.slid = PROC_RptSrc_ARDetail.slid) AND (dbo_rpt_dat_ARDetail.eid = PROC_RptSrc_ARDetail.eid) AND (dbo_rpt_dat_ARDetail.PatName = PROC_RptSrc_ARDetail.PatName)
 AND (dbo_rpt_dat_ARDetail.aid = PROC_RptSrc_ARDetail.aid) AND (dbo_rpt_dat_ARDetail.reportmonth = PROC_RptSrc_ARDetail.monasdt) AND (dbo_rpt_dat_ARDetail.clntid = PROC_RptSrc_ARDetail.clntid) 
SET PROC_RptSrc_ARDetail.arbucket = dbo_rpt_dat_ARDetail.transagebucket;

 

[dhookom]

Neither table has any primary keys

That's your problem. You must have primary/foreign keys in order for a query to be updateable. After you add the keys to the linked tables, you will need to relink.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

If you can't make the database more reliable, you may try someting like tis:
UPDATE PROC_RptSrc_ARDetail
SET arbucket = (
SELECT transagebucket FROM dbo_rpt_dat_ARDetail
WHERE dbo_rpt_dat_ARDetail.slid=PROC_RptSrc_ARDetail.slid AND dbo_rpt_dat_ARDetail.eid=PROC_RptSrc_ARDetail.eid
AND dbo_rpt_dat_ARDetail.PatName=PROC_RptSrc_ARDetail.PatName AND dbo_rpt_dat_ARDetail.aid=PROC_RptSrc_ARDetail.aid
AND dbo_rpt_dat_ARDetail.reportmonth=PROC_RptSrc_ARDetail.monasdt AND dbo_rpt_dat_ARDetail.clntid=PROC_RptSrc_ARDetail.clntid
)
WHERE EXISTS (
SELECT transagebucket FROM dbo_rpt_dat_ARDetail
WHERE dbo_rpt_dat_ARDetail.slid=PROC_RptSrc_ARDetail.slid AND dbo_rpt_dat_ARDetail.eid=PROC_RptSrc_ARDetail.eid
AND dbo_rpt_dat_ARDetail.PatName=PROC_RptSrc_ARDetail.PatName AND dbo_rpt_dat_ARDetail.aid=PROC_RptSrc_ARDetail.aid
AND dbo_rpt_dat_ARDetail.reportmonth=PROC_RptSrc_ARDetail.monasdt AND dbo_rpt_dat_ARDetail.clntid=PROC_RptSrc_ARDetail.clntid
)

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

 

[dhookom]

If all tables involved are from SQL Server, you could use a pass-through query.

Duane
Hook'D on Access
MS Access MVP