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","i sempty","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","i sempty","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","i sempty","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

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

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

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

--0__=0ABBFB73DFFA0FF18f9e8a93df938690918c0ABBFB73DFFA0FF1
Content-type: multipart/alternative;
Boundary="1__=0ABBFB73DFFA0FF18f9e8a93df938690918c0ABBFB73DF FA0FF1"

--1__=0ABBFB73DFFA0FF18f9e8a93df938690918c0ABBFB73DFFA0FF1
Content-type: text/plain; charset="us-ascii"
Content-transfer-encoding: quoted-printable


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"=3D200 WHERE "my_id"=3D8 and xmin=3D=
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.campbell@us.michelin.com



=
=20
Geert Janssens =
=20
=20
e> To=
=20
Sent by: pgsql-odbc@postgresql.org =
=20
pgsql-odbc-owner@ cc=
=20
postgresql.org =
=20
Subject=
=20
[ODBC] Ms Access 2000 - =
=20
09/05/2006 13:17 Update/Delete fails with Write =
=20
conflict =
=20
=
=20
=
=20
=
=20
=
=20
=
=20
=
=20




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=3D155994856, query=3D'fetch 100 in SQL_CUR094664C0'
conn=3D147666896, query=3D'declare SQL_CUR08CD6440 cursor for
SELECT
"invoiceid","number","date","vendorid","type","entrydate","i sempty","dt"
FROM "public"."tvinvoice" WHERE "invoiceid" =3D 14 OR "invoiceid" =3D 15
OR "invoiceid" =3D 16 OR "invoiceid" =3D 17 OR "invoiceid" =3D 18 OR "invoi=
ceid"
=3D
19 OR "invoiceid" =3D 20 OR "invoiceid" =3D 21 OR "invoiceid" =3D 22 OR
"invoiceid"
=3D 23'
conn=3D147666896, query=3D'fetch 100 in SQL_CUR08CD6440'
conn=3D147666896, query=3D'fetch 100 in SQL_CUR08CD6440'
conn=3D147666896, query=3D'close SQL_CUR08CD6440'
conn=3D147666896, query=3D'COMMIT'
....

When selecting last record:

conn=3D147666896, query=3D'declare SQL_CUR08CD6440 cursor for
SELECT
"invoiceid","number","date","vendorid","type","entrydate","i sempty","dt"
FROM "public"."tvinvoice" WHERE "invoiceid" =3D 959 OR "invoiceid" =3D 959
OR "invoiceid" =3D 959 OR "invoiceid" =3D 959 OR "invoiceid" =3D 959 OR
"invoiceid"
=3D 959 OR "invoiceid" =3D 959 OR "invoiceid" =3D 959 OR "invoiceid" =3D 959
OR "invoiceid" =3D 959'
conn=3D147666896, query=3D'fetch 100 in SQL_CUR08CD6440'
conn=3D147666896, query=3D'fetch 100 in SQL_CUR08CD6440'
conn=3D147666896, query=3D'close SQL_CUR08CD6440'
conn=3D147666896, query=3D'COMMIT'
conn=3D155994856, query=3D'declare SQL_CUR094664C0 cursor for
SELECT "vendorid" ,"name" ,"code" ,"defaultcurrency" ,"lastref" ,"comments"

FROM "public"."tvendor" '
conn=3D155994856, query=3D'fetch 100 in SQL_CUR094664C0'
conn=3D155994856, query=3D'fetch 100 in SQL_CUR094664C0'
conn=3D147666896, query=3D'declare SQL_CUR08CD6440 cursor for
SELECT
"invoiceid","number","date","vendorid","type","entrydate","i sempty","dt"
FROM "public"."tvinvoice" WHERE "invoiceid" =3D 857 OR "invoiceid" =3D 858
OR "invoiceid" =3D 859 OR "invoiceid" =3D 860 OR "invoiceid" =3D 861 OR
"invoiceid"
=3D 862 OR "invoiceid" =3D 863 OR "invoiceid" =3D 864 OR "invoiceid" =3D 865
OR "invoiceid" =3D 866'
conn=3D147666896, query=3D'fetch 100 in SQL_CUR08CD6440'
conn=3D147666896, query=3D'fetch 100 in SQL_CUR08CD6440'
conn=3D147666896, query=3D'close SQL_CUR08CD6440'
conn=3D147666896, query=3D'COMMIT'

When trying to update "date":

conn=3D147666896, query=3D'UPDATE "public"."tvinvoice"
SET "date"=3D'2006-09-15'::date WHERE "invoiceid" =3D 959 AND "number"
=3D 'TS-test' AND "date" =3D '2006-09-05'::date AND "vendorid" =3D 185 AND =
"type"

=3D 'Detail' AND "entrydate" =3D '2006-09-05'::date AND "isempty" =3D '0' A=
ND
"dt"
=3D '4711-07-10 00:00:00'::timestamp'
conn=3D147666896, query=3D'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

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

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

--1__=0ABBFB73DFFA0FF18f9e8a93df938690918c0ABBFB73DFFA0FF1
Content-type: text/html; charset=US-ASCII
Content-Disposition: inline
Content-transfer-encoding: quoted-printable

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 t=
est with a simple table. With the ODBC Administrator, I turned on MyLog fo=
r my DSN. I found both used the primary key field and the row versioning fi=
eld. For example: UPDATE "machine_id"=3D200 WHERE "my_id&quo=
t;=3D8 and xmin=3D79.



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 primar=
y 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 wro=
ng SQL, it might be something underlying like the Access version, Jet Engin=
e, MDAC components.



There are some recommended things to do and avoid when making tables to lin=
k for using Access such as

-avoid types Access doesn't understand like int8 (Access has a 4 byte maxim=
um on Longs and Doubles).

-Be careful with TEXT to MEMO and BLOB so they do not map to VARCHARS or so=
mething strange.



Your table doesn't seem to violate these rules.

I believe the timestamp field is no longer necessary with modern implementa=
tion of PostgreSQL and the pgODBC.



There is a small possibility that you have a threading problem. A record i=
s SELECTed in one thread and another thread tries to open a new database co=
nnection 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 pra=
ctice if you hope to achieve portability.





You are doing some of the right steps. I don't know if this will help. Exce=
pt 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.campbell@us.michelin.com



6" height=3D"16" alt=3D"Inactive hide details for Geert Janssens <info@k=
obaltwit.be>">Geert Janssens <info@kobaltwit.be>

A0FF18f9e8a93df9@michelin.com); background-repeat: no-repeat; " width=3D"40= %"> Geert Janssens <info@kobaltwit.be>= Sent by: pgsql-odbc-owner@postgresql.org 09/05/2006 13:17

8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> To "> =3D"0" height=3D"1" width=3D"1" alt=3D""> pgsql-odbc@postgresql.org 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> cc "> =3D"0" height=3D"1" width=3D"1" alt=3D""> 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> Subject "100%"> der=3D"0" height=3D"1" width=3D"1" alt=3D""> [ODBC] Ms Access 2000 - Update/Delete fails with Write con= flict 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> td> helin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D"">

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

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","i sempty","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","i sempty","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","i sempty","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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

Greg,

Thank you for your reply. My comments are mixed in below:

On Tuesday 05 September 2006 22:15, greg.campbell@us.michelin.com wrote:
> First, you might want to try pgODBC version 8.2.xxx (Use Control Panel --
> Add/Remove programs to remove the old)
>
I tried this, but it doesn't seem to change the issue. The update is still not
using the primary key while the select statement does. I did restart my
Access database and relink the tables after the update.

> 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'm not sure about this row versioning field. I have row versioning enabled in
psqlODBC (as was mentioned in one or the other ODBC forum to solve a similar
problem), but I don't know what it is about or what other things should be
configured for it to work. Should you have a good link about this, that would
be welcome.

> 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.
>
A good suggestion ! Unfortunatly, I don't see anything out of the ordinary:
datatypes are as expected, the primary key seems correct, the unique keys (dt
for sure) are indicated as such,...

> 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.
>
The versions I have are:
- Access 2000 (9.0.3821 SR-1)
- Microsoft DAO 3.6
- Microsoft ActiveX Data Objects 2.1
- MDAC (hotfix) 2.53
- I'm not sure where to find the version of Jet, although I believe it is 4.0

> 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.
>
I tried the timestamp based on old mailing list posts. The most recent one was
dated somewhere in 2003. So maybe this is indeed not needed anymore. I found
a mention of timestamps in the documentation that gets shipped with psqlODBC,
about the precision of the seconds. I -perhaps wrongly- interpreted that as a
suggestion this issue is still to be solved by adding the timestamp. If not,
I'd prefer to remove the timestamp field again. It doesn't simplify things
anyway.

> 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.
>
Also I haven't found how to verify this. Would MyLog reveal this ?

> 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 quite right. I was already aware of the poor choice of date as a field
name, the others are new (although obvious when looking back at it). If I get
this Write conflict error out of the way, I intend rename the fields and
propagate the changes throug all the related queries and code (sigh...).

>
> 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.
>
Thanks again. Although I haven't fixed this issue yet, you helped me already
with a more general background and I would never have come up with the
Documenter. It will help me further in other debuggings as well.

Regards,

Geert

--
Kobalt W.I.T.
Web & Information Technology
Brusselsesteenweg 152
1850 Grimbergen

Tel : +32 479 339 655
Email: info@kobaltwit.be

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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","i sempty","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","i sempty","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","i sempty","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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings