I expect what I am reporting is already a known problem, but since I haven't heard anyone else
report it I thought I should at least mention it. I am also looking for suggestions from anyone
about how they prefer to bind forms to multiple hierarchial tables in access. I tried to wrap
them is a view but it doesn't work all that well as you will see below.
I have implemented update rules on a test view in postgresql. When ever I
insert/update/delete from the view using ONLY THE SURROGATE KEY in the where clause all data
modifications work perfectly. However, if you reference actual column values in where clause
inconsistent updates occur.
For example vwife is the view.
postgres=# select * from vwife;
id | name | dresssize
---+---------+-----------
3 | dodie | 13
4 | heather | 10
2 | katie | 11
(3 rows)
postgres=# update vwife
set name = 'Katheryn',
dresssize = 12
where (id,name,dresssize)=(2,'katie',11);
UPDATE 0
postgres=# select * from vwife;
id | name | dresssize
----+----------+-----------
3 | dodie | 13
4 | heather | 10
2 | Katheryn | 11
^^^^^^^^ <-- update 0 is false
Unfortunately when connecting to an update-able view as a linked table in MS-access, access
identifies which tuple to update by specifying every element in the view in the where clause.
This obviously makes an update-able view unusable as a linked table in MS-access.
Hiroshi Inoue on the ODBC list suggested some work arounds that really helped, but pointed out
that it was not a perfect solution and other inconsistent changes may occur.
http://archives.postgresql.org/pgsql-odbc/2006-12/msg00036.php
View and rule definitions are found here:
http://archives.postgresql.org/pgsql-odbc/2006-12/msg00034.php
Is this a known bug? Does anyone have suggests how I can get around this problem?
Regards,
Richard Broersma Jr.
