Re: Ms Access 2000 - Update/Delete fails with Write conflict
От | Geert Janssens |
---|---|
Тема | Re: Ms Access 2000 - Update/Delete fails with Write conflict |
Дата | |
Msg-id | 200609061304.51657.info@kobaltwit.be обсуждение исходный текст |
Ответ на | Ms Access 2000 - Update/Delete fails with Write conflict (Geert Janssens <info@kobaltwit.be>) |
Список | pgsql-odbc |
I found my problem, thanks to the suggestions made by Greg Campbell: I hadrow versioning was enabled in ODBC, but in order for Ms Access to use it, I had to remove and relink all the ODBC linked tables. Thank you very much for your help and suggestions ! Regards, Geert On Tuesday 05 September 2006 19:17, 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 -- Kobalt W.I.T. Web & Information Technology Brusselsesteenweg 152 1850 Grimbergen Tel : +32 479 339 655 Email: info@kobaltwit.be
В списке pgsql-odbc по дате отправления:
Предыдущее
От: Geert JanssensДата:
Сообщение: Re: Ms Access 2000 - Update/Delete fails with Write conflict