Re: record locks?

Thanks for responding, clearly i should have been more specific in my description of the problem.

The linked table is a table, created in postgres with the CREATE TABLE command.
I am trying to update boolean fields through check boxs in Access datesheet view, and also through bound controls on forms.

I can insert a record through the Access datasheet view by typing a value into the key field in the '*' record
(the 'add new record' record)
After that I can successfully update fields only if i do so before i leave that record.
After that, any other changes I try to make to the data, for example check a check box to
update a boolean field, i get the "the record has been changed by another user..." message and changes are dropped.
I can make updates to the table subsequently only if i execute sql commands with the Access execute function.
Since that form of update works, executing a sql query in access, the system is correctly locating records by the key field value.

When I ran the odbc driver install i unchecked the 'bool as char' box.

I am actually doing no explicit record locking,

No one else is locking the record because we are testing a new system in a very small development community (2 developers) and the other person is not here!

When our postgres/unix administrator returns i will ask him to turn on psqlodbc logging to see what postgres thinks of attempts to update the fields through bound Access controls and the Access datasheet view.

Thanks for your help with this matter!

Fred Parkinson

>>> "Tambet Matiisen" <t(dot)matiisen(at)aprote(dot)ee> 02/07/03 01:11AM >>>
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

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