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

First, you might want to try pgODBC version 8.2.xxx (Use Control Panel --
Add/Remove programs to remove the old)

In your log, the UPDATE statement has a WHERE clause that tells us that the
primary key is not being used for the update.
yet the SELECT does use the primary key.

This suggest that Access/Jet is not fully understanding the primary key OR
not always using it when it should.
I do not have Access 2000, but I do have Access'97 and Access XP. I ran a
test with a simple table. With the ODBC Administrator, I turned on MyLog
for my DSN. I found both used the primary key field and the row versioning
field. For example: UPDATE "machine_id"=200 WHERE "my_id"=8 and xmin=79.

I suggest checking what Access thinks your metadata is:
Use Tools->Analyze->Documenter and select the tables of concern.
Use the Options button to select "Include For Indexes...", Select Names,
Fields, and Properties.
Click OK to run the report. It should report your keys, and for your
primary key, it should report Primary as True.

If something here is off (data types, primary/unique keys)...you need to
re-link or drop tables and reattach.

If Access does understand your metadata, but is still is generating the
wrong SQL, it might be something underlying like the Access version, Jet
Engine, MDAC components.

There are some recommended things to do and avoid when making tables to
link for using Access such as
-avoid types Access doesn't understand like int8 (Access has a 4 byte
maximum on Longs and Doubles).
-Be careful with TEXT to MEMO and BLOB so they do not map to VARCHARS or
something strange.

Your table doesn't seem to violate these rules.
I believe the timestamp field is no longer necessary with modern
implementation of PostgreSQL and the pgODBC.

There is a small possibility that you have a threading problem. A record
is SELECTed in one thread and another thread tries to open a new database
connection and update the record. I am afraid I do not recall a solution,
but seem to remember setting the Jet Connection to never timeout (0), but
that may have been for a different issue.

This all said, you have a table with fields named
number
date
type.
I have worked across a number of database, and this seems not be a best
practice if you hope to achieve portability.

You are doing some of the right steps. I don't know if this will help.
Except for the driver version I feel like I have not given any specific
advice, but good luck.

Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg(dot)campbell(at)us(dot)michelin(dot)com

Geert Janssens
<info(at)kobaltwit(dot)b
e> To
Sent by: pgsql-odbc(at)postgresql(dot)org
pgsql-odbc-owner@ cc
postgresql.org
Subject
[ODBC] Ms Access 2000 -
09/05/2006 13:17 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq