Re: Ms Access 2000 - Update/Delete fails with Write conflict - Mailing list pgsql-odbc
From | Hiroshi Inoue |
---|---|
Subject | Re: Ms Access 2000 - Update/Delete fails with Write conflict |
Date | |
Msg-id | 44FE446D.60105@tpf.co.jp Whole thread Raw |
In response to | Ms Access 2000 - Update/Delete fails with Write conflict (Geert Janssens <info@kobaltwit.be>) |
List | pgsql-odbc |
Could you send me the Mylog output ? regards, Hiroshi Inoue Geert Janssens wrote: > Hi, > > I'm afraid this problem has been mentioned more than once on this list before. > I tried all suggestions for solutions I could find, but I can't seem to fix > this: > > I have a database in PostgreSQL version 8.0.8, and a frontend created in Ms > Access 2000, connecting to the database via psqlODBC version 8.01.02. > > This particular table was created as follows: > CREATE TABLE tvinvoice ( > invoiceid serial NOT NULL, > number character varying(50) NOT NULL, > date date, > vendorid integer NOT NULL, > "type" character varying(50) NOT NULL, > entrydate date DEFAULT ('now'::text)::date, > isempty boolean, > dt timestamp(0) without time zone NOT NULL > ); > ALTER TABLE ONLY tvinvoice > ADD CONSTRAINT tvinvoice_dt_key UNIQUE (dt); > ALTER TABLE ONLY tvinvoice > ADD CONSTRAINT tvinvoice_number_key UNIQUE (number); > ALTER TABLE ONLY tvinvoice > ADD CONSTRAINT tvinvoice_pkey PRIMARY KEY (invoiceid); > ALTER TABLE ONLY tvinvoice > ADD CONSTRAINT "$1" FOREIGN KEY (vendorid) REFERENCES tvendor(vendorid) > ON UPDATE CASCADE ON DELETE RESTRICT; > > Note: the dt timestamp field was added later in an effort to fix the problem > I'll describe just later on. > > In the Access database, I have a link to this table, and a form in to > manipulate it. > > Now I can add new records with no problem to this table via the form, but if I > try to update or delete existing records, I get the error: > > Write conflict: > This record has been changed by another user since you started > editing it. If you save the record, you will overwrite the changes > the other user made. > Copying the changes to the clipboard will let you look at the values > the other user entered, and then paste your changes back in if you > decide to make changes. > > I can't choose Save Record at this point, only copy to clipboard or drop > changes. > > * My first attempt was to add a timestamp field with a unique constraint. I > found this solution in some faq on the internet. For the records that were > already in this table, I used to_timestamp('invoiceid','J') to set an initial > (and unique) timestamp. ('J' is the Julian days since 4xxx BC, so since each > invoiceid is unique, the timestamp generated from it, should also be). > > I relinked the table, added the timestamp to the form, and tried to update a > record again. The error kept coming back. > > * Next I found in the faq that is distributed with psqlODBC that the seconds > precision had changed, which could cause the same problem. So in Postgres, I > updated the field definition to timestamp(0) as per the faq. > > I relinked the table, added the timestamp to the form, and tried to update a > record again. The error kept coming back. > > * I also found a message stating that row versioning should be enabled in the > ODBC connection setting. I tried this as well with no luck. > > I also logged some of the queries that MS Access performs via the psql_comm > log. Here are the results: > > When opening form: > > conn=155994856, query='fetch 100 in SQL_CUR094664C0' > conn=147666896, query='declare SQL_CUR08CD6440 cursor for > SELECT "invoiceid","number","date","vendorid","type","entrydate","isempty","dt" > FROM "public"."tvinvoice" WHERE "invoiceid" = 14 OR "invoiceid" = 15 > OR "invoiceid" = 16 OR "invoiceid" = 17 OR "invoiceid" = 18 OR "invoiceid" = > 19 OR "invoiceid" = 20 OR "invoiceid" = 21 OR "invoiceid" = 22 OR "invoiceid" > = 23' > conn=147666896, query='fetch 100 in SQL_CUR08CD6440' > conn=147666896, query='fetch 100 in SQL_CUR08CD6440' > conn=147666896, query='close SQL_CUR08CD6440' > conn=147666896, query='COMMIT' > ... > > When selecting last record: > > conn=147666896, query='declare SQL_CUR08CD6440 cursor for > SELECT "invoiceid","number","date","vendorid","type","entrydate","isempty","dt" > FROM "public"."tvinvoice" WHERE "invoiceid" = 959 OR "invoiceid" = 959 > OR "invoiceid" = 959 OR "invoiceid" = 959 OR "invoiceid" = 959 OR "invoiceid" > = 959 OR "invoiceid" = 959 OR "invoiceid" = 959 OR "invoiceid" = 959 > OR "invoiceid" = 959' > conn=147666896, query='fetch 100 in SQL_CUR08CD6440' > conn=147666896, query='fetch 100 in SQL_CUR08CD6440' > conn=147666896, query='close SQL_CUR08CD6440' > conn=147666896, query='COMMIT' > conn=155994856, query='declare SQL_CUR094664C0 cursor for > SELECT "vendorid" ,"name" ,"code" ,"defaultcurrency" ,"lastref" ,"comments" > FROM "public"."tvendor" ' > conn=155994856, query='fetch 100 in SQL_CUR094664C0' > conn=155994856, query='fetch 100 in SQL_CUR094664C0' > conn=147666896, query='declare SQL_CUR08CD6440 cursor for > SELECT "invoiceid","number","date","vendorid","type","entrydate","isempty","dt" > FROM "public"."tvinvoice" WHERE "invoiceid" = 857 OR "invoiceid" = 858 > OR "invoiceid" = 859 OR "invoiceid" = 860 OR "invoiceid" = 861 OR "invoiceid" > = 862 OR "invoiceid" = 863 OR "invoiceid" = 864 OR "invoiceid" = 865 > OR "invoiceid" = 866' > conn=147666896, query='fetch 100 in SQL_CUR08CD6440' > conn=147666896, query='fetch 100 in SQL_CUR08CD6440' > conn=147666896, query='close SQL_CUR08CD6440' > conn=147666896, query='COMMIT' > > When trying to update "date": > > conn=147666896, query='UPDATE "public"."tvinvoice" > SET "date"='2006-09-15'::date WHERE "invoiceid" = 959 AND "number" > = 'TS-test' AND "date" = '2006-09-05'::date AND "vendorid" = 185 AND "type" > = 'Detail' AND "entrydate" = '2006-09-05'::date AND "isempty" = '0' AND "dt" > = '4711-07-10 00:00:00'::timestamp' > conn=147666896, query='ROLLBACK' > > Obviously MS Access is not using dt as a unique field to identify the records, > but I can't find out why. > > I have no other ideas to try anymore. Does anybody else do ? I'll gladly > provide more details should the above not be sufficient. > > Regards, > > > Geert Janssens
pgsql-odbc by date: