updatable views and MS-Access 2K

From: lutz(dot)willerding(at)gmail(dot)com (Lutz Willerding)
To: pgsql-odbc(at)postgresql(dot)org
Subject: updatable views and MS-Access 2K
Date: 2004-11-05 23:44:51
Message-ID: eb4b01ed.0411051544.3b33158c@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

how do you do it?

looking for a solution, I found this thread just some lines below:
update rules + views + ....
which suggests using ctid, causing problems with views using more than
1 table.
for views using 2 tables, I found the following partial solution:

1: updatable views need a 'catch all rule'; docs indicate to do
something like:
'create or replace rule UpdRuleCatchAll as on update myview do instead
nothing;'

It looks like, this 'nothing' makes access moan about 'no record'.
'do nothing' replaced by:
'do select * from myview where <YourPrimaryKeySpecHere>'
returns 1(!) record. -> no 'record missing' any more.

2: do specific update rules with '...where OLD.YourKey is NULL or
OLD.yourkey <> NEW.yourkey (update or insert the table involved) where
(use exactly the same keys as you will give access as the primary
key(s) when importing the view as a linked table)

didn't try to do insert or delete rules yet; using a form, I just call
a stored procedure on a 'OnClick'-Event that is designed to do what is
needed, and the .Requery of the matching MS-Access-Object.

This works _without_ including ctid in the views definition _and_ it
works with 2 tables involved. (just trying to get the third)

Browse pgsql-odbc by date

  From Date Subject
Next Message Rodrigo Carvalhaes 2004-11-07 19:15:08 Snapshots
Previous Message Lothar Behrens 2004-11-03 19:41:04 ODBC understanding problem