record locks?

Whenever I edit a postgresql record from a MS Access datasheet view or bound control, it seems to lock the record forever and subsrquent edits result in "The record has been changed by another user blah blah ..." and I can no longer edit that record from the datasheet view or bound control (though I can execute a SQL query to update it).

We are using postgresql 7.2 and ODBC driver 7.02.00.01

This persists even if I delete the linked table and relink it, or close and restart Access.

Any ideas?

Fred Parkinson

Is your linked table actually a view with insert/update/delete rules? Then
the update through bound controls will not work in 7.2. 7.3 resolves the
issue.

Otherwise the problem could be float (or other non-exact) columns in your
table. Access remembers old values of record and when updating, it adds them
to where condition:

UPDATE table SET col1 = newvalue WHERE id = old_id AND col1 = oldvalue1 AND
col2 = oldvalue2;

The mentioned error is returned when the update returns 0 modified rows -
someone has changed the record and old values are not current any more. This
is called optimistic locking.

The problem is, that when Access remembers float values, it sometimes rounds
them a bit.

select 1/3::float, 0.333333333333333, 1/3::float = 0.333333333333333;

?column? | ?column? | ?column?
-------------------+-------------------+----------
0.333333333333333 | 0.333333333333333 | f
(1 row)

So the optimistic locking condition is not true any more, although the
record has not changed.

You have to switch on psqlodbc logging and see, what query Access executes.
Then you have determine, what column is causing the error. Then you could
change datatype of the column. Or you could build a view which rounds the
value, add insert/update/delete rules to the view and do all database
manipulation through view. I have seen this problem before in this list, so
maybe someone has better solution.

Tambet

----- Original Message -----
From: Fred Parkinson
To: pgsql-odbc(at)postgresql(dot)org
Sent: Thursday, February 06, 2003 10:09 PM
Subject: [ODBC] record locks?

Whenever I edit a postgresql record from a MS Access datasheet view or
bound control, it seems to lock the record forever and subsrquent edits
result in "The record has been changed by another user blah blah ..." and I
can no longer edit that record from the datasheet view or bound control
(though I can execute a SQL query to update it).

We are using postgresql 7.2 and ODBC driver 7.02.00.01

This persists even if I delete the linked table and relink it, or close and
restart Access.

Any ideas?

Fred Parkinson