Обсуждение: 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@kobaltwit.be
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
Вложения
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","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
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
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@kobaltwit.be