Skip site navigation (1) Skip section navigation (2)

Views, Access 2000 and write conflicts

From: Nick Haw <nick(dot)haw(at)operagrp(dot)com>
To: "'pgsql-odbc(at)postgresql(dot)org'" <pgsql-odbc(at)postgresql(dot)org>,pgsql-interfaces(at)postgresql(dot)org
Subject: Views, Access 2000 and write conflicts
Date: 2002-06-26 09:29:37
Message-ID: 71F468541618D3118DDF204C4F4F5020775DF4@MAPSERVER (view raw or flat)
Thread:
Lists: pgsql-interfacespgsql-odbc
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



Responses

pgsql-odbc by date

Next:From: Annissa MelhenasDate: 2002-06-26 09:31:04
Subject: SQLDriverConnect
Previous:From: Neil ConwayDate: 2002-06-26 01:52:38
Subject: Re: "Field is too small"

pgsql-interfaces by date

Next:From: Friedrich DodtDate: 2002-06-26 10:35:17
Subject: Re: Where is PLbash ??
Previous:From: Karel ZakDate: 2002-06-26 07:49:39
Subject: Re: Where is PLbash ??

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group