Query non updateable- not sure why?

[KevinSlater]

Hi,

We have the following SQL code in a access 2003 but we need to be able to add new records within the query but it wont allow this. The table named "dbo_view_cbprices" is a linked SQL table view and im thinking perhaps this is the the route of the problem, any help on this would be appreciated.


SELECT DISTINCT t1.stk_part_code,
t1.price_current,
t1.price_new,
t1.com_price_reference,
t0.cardcode,
t0.cardname,
t0.listnum,
t0.listname,
t0.itemcode,
t0.price,
t0.currency,
t2.prom_id,
t2.cardcode
FROM ([DATA-PROM] AS t2
INNER JOIN [DATA-PROM_PRICES] AS t1
ON t2.prom_id = t1.prom_id)
LEFT JOIN dbo_view_cbprices AS t0
ON ( t1.stk_part_code = t0.itemcode )
AND ( t1.com_price_reference = t0.listname )
ORDER BY t0.itemcode;

 

[boblarson]

I'd assume it was the mixed joins. See here for more about why it can be non-updateable.

 

[KevinSlater]

Thanks for the quick reply Bob, the weblink you provided is helpful.

Like you say it sounds like its either the multiple Join types or the fields in a join are not indexed correctly.

 

[JANR]

The keyword DISTINCT also will make it un-Updateble

JR

 

[KevinSlater]

ok, i tried taking DISTINCT out but the query is still non updateable