Search Postgresql Archives

Are updateable view as a linked table in ms-access a bad idea?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux