Обсуждение: 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.campbell@us.michelin.comGeert Janssens <info@kobaltwit.be>
Sent by: pgsql-odbc-owner@postgresql.org 09/05/2006 13:17 |
|
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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
