Update view in PostgreSQL and use view in Access to update
I have created a view in PosgreSQL with the following update rule
It works to run the following update query on the view in PostgreSQL but not in Microsoft Access when running the same query
Can someone help on why that same query would not work in Access when linking to that view in PostgreSQL?
In Access I get this error
CREATE OR REPLACE RULE "_UPDATE" AS
ON UPDATE TO custom.qty_brk_price DO INSTEAD UPDATE inventory_price_matrix SET amount = new.amount
FROM inventory_price_matrix ipm
JOIN custom.qty_brk_price ON ipm.part_no::text = qty_brk_price.part_no::text
WHERE inventory_price_matrix.part_no::text = new.part_no::text AND inventory_price_matrix.whse::text = '00'::text AND inventory_price_matrix.promo_code::text = 'BV_QTYBRK'::text AND inventory_price_matrix.amount_type::text = 'P'::text AND inventory_price_matrix.uom_code::text <> ''::text AND qty_brk_price.qty_brk_price = old.qty_brk_price AND inventory_price_matrix.min_qty = old.min_qty;It works to run the following update query on the view in PostgreSQL but not in Microsoft Access when running the same query
UPDATE custom.qty_brk_price SET amount = 139
WHERE part_no='10006';Can someone help on why that same query would not work in Access when linking to that view in PostgreSQL?
In Access I get this error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found our own solution
One thing I would ask is when you configured the ODBC connection that you are using from Access to PostgreSQL did you mark it as read only, if so that will be a problem. Also, does the user that you are connecting with have update access to the database / tables?
»bp