record locks? same problem

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

-----Ursprungliche 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