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(dot)haw(at)operagrp(dot)com]
> Sent: Wednesday, June 26, 2002 2:30 AM
> To: 'pgsql-odbc(at)postgresql(dot)org'; pgsql-interfaces(at)postgresql(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>