Re: Ms Access 2000 - Update/Delete fails with Write conflict

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(at)kobaltwit(dot)be