Re: [ODBC] Views, Access 2000 and write conflicts

Do you have the update trigger on the main table also?  I've run into this
when a trigger modifies the data Access freaks out thinking there is a write
conflict.  I found some code posted to the pgsql-interfaces list that
appears to help out a lot.  You can find it here:
http://archives.postgresql.org/pgsql-interfaces/2000-11/msg00303.php

I hope this helps.

Rob

> -----Original Message-----
> From: Nick Haw [mailto:nick.haw@operagrp.com]
> Sent: Wednesday, June 26, 2002 2:30 AM
> To: 'pgsql-odbc@postgresql.org'; pgsql-interfaces@postgresql.org
> Subject: [ODBC] Views, Access 2000 and write conflicts
>
>
> Hello all
>
> I am having a major problem with a view I have created. I
> want users to be
> able to access it in  using a form in access 97 and update some of the
> underlying data. I created the view and using my ODBC
> connection I linked
> the view to the Access database. Below is the sql I used to
> create the view.
>
>
>
> CREATE TABLE "vw_invoicerestricted" (
>   "fldinvoicenumber" int4 NOT NULL,
>   "fldinvoicedate" date NOT NULL,
>   "fldinvoicecompany" int4 NOT NULL,
>   "fldinvoiceclient" int4,
>   "fldsurveynumber" int4,
>   "fldtheirref" varchar(50),
>   "flddescription" varchar(255) NOT NULL,
>   "fldchargevat" int4,
>   "fldcompany" varchar(50),
>   "flddivision" int4,
>   "fldpaid" int4,
>   "fldpaiddate" date,
>   "fldcreditnote" int4,
>   "fldinternalinvoice" int4,
>   "fldoldinvoicecompany" varchar(255),
>   "fldoldinvoiceclient" varchar(255),
>   "flddateadded" date,
>   "flduseradded" varchar(15),
>   "flddatemodified" date,
>   "fldusermodified" varchar(15),
>   CONSTRAINT "vw_invoicerestricted_pkey" PRIMARY KEY
> ("fldinvoicenumber")
> ) WITH OIDS;
>
> CREATE RULE "_RETvw_invoicerestricted" AS ON SELECT TO
> vw_invoicerestricted
> DO INSTEAD SELECT * FROM tblinvoice WHERE fldcreditnote = 0;
>
> CREATE RULE vw_invoicerestricted_upd_rule AS ON UPDATE TO
> vw_invoicerestricted
> DO INSTEAD
> UPDATE tblinvoice SET fldpaid=NEW.fldpaid,
> fldpaiddate=NEW.fldpaiddate WHERE
>
> fldinvoicenumber=NEW.fldinvoicenumber;
>
> I am able to update the data using psql and pgadminII, but as
> soon as I try
> to update it using access it brings up a message "Write Conflict" This
> record has been changed by another user since you started
> editing it.......
> Is there any reason for this? This behaviour only happens in
> the view, if i
> try to update the same data in the table the view is "looking
> at" Access
> 2000 doesn't have a problem with it.
>
> Hope someone can help, because my sanity is suffering
>
> Many Thanks
>
> Nick Haw
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>