Re: record locks? same problem

Keep your sanity by repeating "help is on the way"
If only it were true. However, sanity is not always based on reality (is that good or bad?)

I finally solved my 'the record has been changed by another user...'
I install an ODBC datasourse through windows control panel.
As the last part of the installation a window pops up that says
"Please supply any missing information needed to connect"
in driver ver 7.02.00.01 there is a button labeled "connection"
in driver ver 7.02.00.05 there is a button labeled "page 2" (or was it page 1 with this option?)

this reveals an option "row versioning"
check the box to turn row versioning on

that did it, no more error.

>>> "Henry Alfke" <henry(at)reetec(dot)de> 02/14/03 01:46AM >>>

Hello together,

i have the same problem with one table "the record has been changed by another user..." with the same configuration as Fred
but at another table on postgres it works fine with only one boolean field.

i created both tables from an MS Access Database with the Migration Wizard from pgadmin II

i tested the "problem" table with a view from MS Access (with an without the boolean fields)
with boolean fields in the view i get the error message "the record has been changed by another user..." on an update of a field
without the boolean fields it works fine

when i use sql statements from VBA, i have to use a type cast like sqlstring = "... where cbool(mybooleanfield) = true" as a workaround, this will work also

any ideas, why i have problem with one table ?

At least, you all made a great job on postgres, thank you !!
postgres is the most productive tool i ever seen,

best regards

henry

-----Ursprüngliche Nachricht-----
Von: pgsql-odbc-owner(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org]Im Auftrag von Fred Parkinson
Gesendet: Donnerstag, 13. Februar 2003 22:05
An: t(dot)matiisen(at)aprote(dot)ee; pgsql-odbc(at)postgresql(dot)org
Betreff: Re: [ODBC] 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