Thread: Views, Access 2000 and write conflicts

Views, Access 2000 and write conflicts

From
Nick Haw
Date:
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



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

From
Hiroshi Inoue
Date:
Nick Haw wrote:
>
> 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.

Is your server's version 7.2 ? If so, it's a known bug.
Please look at the thread in pgsql-general [Using views
and MS access via odbc] e.g.
http://archives.postgresql.org/pgsql-general/2002-05/msg00170.php.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/