Re: War Story; Error 3197 in MS Access w/ Oracle. Solved.

I believe Oracle has something equivalent to the Microsoft SQl Server TIMESTAMP field which is a binary value that is unique on the server and changes whenever a record is updated. If a table has such a field, Access will use that field for optimistic locking instead of checking the values of all the fields. This is fully documented by Microsoft.

If the TIMESTAMP column is not used, performance can suffer, and optimistic locking may fail if you have date/time or real/float columns in your table. Furthermore, Access will not detect changes to TEXT (Access Memo) columns in a table without a TIMESTAMP column, so changes can be completed when they should be cancelled under optimistic locking.

scaddenp wrote in message <7r3vg7$luh$1_at_newshost.comnet.co.nz>...
>I was getting the dreaded 3197 error (record edited by another user) when
>trying to
>edit or delete records in an ODBC oracle table using MS-ACCESS. Tried 3
ODBC
>drivers
>and updated to latest Jet engine to no avail. Tracking the problem with
>trace I noticed the
>ghastly behaviour of JET in checking the value of every field in a record
>before doing an
>update (there has to be a better way of multiuser access than that!!). The
>representation of data in one or more field must be different between
Oracle
>and Access was my conclusion. Furthermore the problems were confined to
only
>one table and then only to records in that table which were inserted into
>the database by an application running on a unix workstation. While
>suspicion fell on floating point fields, these worked okay in the other
>tables. The only field types exclusive to the problem table was some date
>and date-time fields. I stopped the unix application from loading these
>fields and records could be edited. By elimination I tracked it to ONE
>date-time field. Why the problem? I compared the unix code writing these
>fields and
>saw a slight difference in the format of the date/time passed to Oracle.
The
>date time
>showed correctly in both MS access and Oracle, but by changing that format
>to match the other working date/time (yyyy-mm-dd hh:mm:ss), the 3197
problem
>went away.
> This might be a very special case for a 3197 problem but I would look very
>hard at ODBC trace or the SQL in Oracle session manager to look for similar
>problems with
>number representation.
>
>
>
Received on Tue Sep 07 1999 - 19:54:47 CDT