Ms Access 2000 - Update/Delete fails with Write conflict

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