Write Conflict: This record has been changed ...

Hi,

we're using Access97 as frontend to a Oracle db. In Access we have a
bound form which is using a Dynaset as source, global locking and form
locking is off.

All worked well until yesterday. We dublicated some records for a new
session in the Oracle db and since then some of these records cannot
be edited from Access. An error occurs: "This record has been changed
by another user since you started editing...". We are in a single-user
environment, so there's no other user, and no second form has opened
the same table. The Oracle table contains just a few string and number
fields.

I tried to delete and re-insert the records in the Oracle, I compacted
and repaired the Access frontend db - no success.

Does anybody know what wrong here?

Regards
Markus.

re: Write Conflict: This record has been changed ...

On 2 Mar 2004 23:23:58 -0800, markus_fried@gmx.de wrote:
[color=blue]
>Hi,
>
>we're using Access97 as frontend to a Oracle db. In Access we have a
>bound form which is using a Dynaset as source, global locking and form
>locking is off.
>
>All worked well until yesterday. We dublicated some records for a new
>session in the Oracle db and since then some of these records cannot
>be edited from Access. An error occurs: "This record has been changed
>by another user since you started editing...". We are in a single-user
>environment, so there's no other user, and no second form has opened
>the same table. The Oracle table contains just a few string and number
>fields.
>
>I tried to delete and re-insert the records in the Oracle, I compacted
>and repaired the Access frontend db - no success.
>
>Does anybody know what wrong here?
>
>Regards
>Markus.[/color]

Access doesn't handle certain data types well in linked tables, specifically,
floating point numbers and date/time values that can contain milliseconds.
Access uses a where clause to compare the existing data with the pre-edit
data, and this can fail due to rounding error when converting to text and back
again since floating point fractional numbers may not have an exact decimal
representation and Access doesn't understand times with increments smaller
than 1 second.

Mostly, if you try to use Number, Decimal, or Money types rather than Real or
Float types, and avoid anything that could put milliseconds into a time value,
Access is happy.

re: Write Conflict: This record has been changed ...

With SQL Server, this is almost always caused by a BIT field.

Somewhere in that table is a BIT field w/o a value...here's what you need to
do:

1) Run an UPDATE query, setting all NULL bit fields to 0.

2) On the SQL Server side, set a default value of 0 for the BIT fields. (or, on
the Access side, make sure you provide the value)

Not sure if this logic works for ORACLE, but I bet it will...it's more a
function of the crappy ODBC/JET driver.

re: Write Conflict: This record has been changed ...

The SQL Server "Timestamp" data type can help with these things. Does
Oracle have an equivalent?

(Note to readers: Timestamp is *not* a DateTime type; it's a counter
maintained by the server for the purpose of determining whether a
recod has been edited.)

-Matt

On Wed, 03 Mar 2004 08:46:36 GMT, Steve Jorgensen
<nospam@nospam.nospam> wrote:
[color=blue]
>Access uses a where clause to compare the existing data with the pre-edit
>data[/color]

re: Write Conflict: This record has been changed ...

Hi,

thanks for your help - I found out about the float problems shortly
after posting this here. The solution was simple: Do a SQL to round
all values to 10 digits. I don't know what the max number of digits in
a float is that Access can handle, but 10 are ok for me.

Again thanks for your help.

Markus.

Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<l07b4014b4o45p7j6k15a5q4cai1ggcn1l@4ax.com>. ..[color=blue]
> On 2 Mar 2004 23:23:58 -0800, markus_fried@gmx.de wrote:
>
> Access doesn't handle certain data types well in linked tables, specifically,
> floating point numbers and date/time values that can contain milliseconds.
> Access uses a where clause to compare the existing data with the pre-edit
> data, and this can fail due to rounding error when converting to text and back
> again since floating point fractional numbers may not have an exact decimal
> representation and Access doesn't understand times with increments smaller
> than 1 second.
>
> Mostly, if you try to use Number, Decimal, or Money types rather than Real or
> Float types, and avoid anything that could put milliseconds into a time value,
> Access is happy.[/color]

re: Write Conflict: This record has been changed ...

Well, that's a partial solution, but not perfect. See, the problem is that
the rounded decimal values won't all convert perfectly into binary fractions.
For the most part, they will all convert the same kind of wrong, but not
necessarily, so you can still have problems. It's better to change the data
type to a NUMBER or DECIMAL type with a fixed precision.

On 3 Mar 2004 23:20:57 -0800, markus_fried@gmx.de wrote:
[color=blue]
>Hi,
>
>thanks for your help - I found out about the float problems shortly
>after posting this here. The solution was simple: Do a SQL to round
>all values to 10 digits. I don't know what the max number of digits in
>a float is that Access can handle, but 10 are ok for me.
>
>Again thanks for your help.
>
>Markus.
>
>Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<l07b4014b4o45p7j6k15a5q4cai1ggcn1l@4ax.com>. ..[color=green]
>> On 2 Mar 2004 23:23:58 -0800, markus_fried@gmx.de wrote:
>>
>> Access doesn't handle certain data types well in linked tables, specifically,
>> floating point numbers and date/time values that can contain milliseconds.
>> Access uses a where clause to compare the existing data with the pre-edit
>> data, and this can fail due to rounding error when converting to text and back
>> again since floating point fractional numbers may not have an exact decimal
>> representation and Access doesn't understand times with increments smaller
>> than 1 second.
>>
>> Mostly, if you try to use Number, Decimal, or Money types rather than Real or
>> Float types, and avoid anything that could put milliseconds into a time value,
>> Access is happy.[/color][/color]

re: Write Conflict: This record has been changed ...

We already use the Oracle NUMBER (sorry for using the term 'float')
data type (the Oracle tables are linked via ODBC into Access) - so I
don't know what I should change here.....



Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<b7rd40dgjed8ddut52of0qpjed8642ncfo@4ax.com>. ..[color=blue]
> Well, that's a partial solution, but not perfect. See, the problem is that
> the rounded decimal values won't all convert perfectly into binary fractions.
> For the most part, they will all convert the same kind of wrong, but not
> necessarily, so you can still have problems. It's better to change the data
> type to a NUMBER or DECIMAL type with a fixed precision.
>
> On 3 Mar 2004 23:20:57 -0800, markus_fried@gmx.de wrote:
>[color=green]
> >Hi,
> >
> >thanks for your help - I found out about the float problems shortly
> >after posting this here. The solution was simple: Do a SQL to round
> >all values to 10 digits. I don't know what the max number of digits in
> >a float is that Access can handle, but 10 are ok for me.
> >
> >Again thanks for your help.
> >
> >Markus.
> >
> >Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<l07b4014b4o45p7j6k15a5q4cai1ggcn1l@4ax.com>. ..[color=darkred]
> >> On 2 Mar 2004 23:23:58 -0800, markus_fried@gmx.de wrote:
> >>
> >> Access doesn't handle certain data types well in linked tables, specifically,
> >> floating point numbers and date/time values that can contain milliseconds.
> >> Access uses a where clause to compare the existing data with the pre-edit
> >> data, and this can fail due to rounding error when converting to text and back
> >> again since floating point fractional numbers may not have an exact decimal
> >> representation and Access doesn't understand times with increments smaller
> >> than 1 second.
> >>
> >> Mostly, if you try to use Number, Decimal, or Money types rather than Real or
> >> Float types, and avoid anything that could put milliseconds into a time value,
> >> Access is happy.[/color][/color][/color]

re: Write Conflict: This record has been changed ...

Hmm, if they were alreasy NUMBER, I'm surprised that was the problem. I've
only seen the problem when a floating point type was used on the server.

On 4 Mar 2004 23:11:51 -0800, markus_fried@gmx.de wrote:
[color=blue]
>We already use the Oracle NUMBER (sorry for using the term 'float')
>data type (the Oracle tables are linked via ODBC into Access) - so I
>don't know what I should change here.....
>
>
>
>Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<b7rd40dgjed8ddut52of0qpjed8642ncfo@4ax.com>. ..[color=green]
>> Well, that's a partial solution, but not perfect. See, the problem is that
>> the rounded decimal values won't all convert perfectly into binary fractions.
>> For the most part, they will all convert the same kind of wrong, but not
>> necessarily, so you can still have problems. It's better to change the data
>> type to a NUMBER or DECIMAL type with a fixed precision.
>>
>> On 3 Mar 2004 23:20:57 -0800, markus_fried@gmx.de wrote:
>>[color=darkred]
>> >Hi,
>> >
>> >thanks for your help - I found out about the float problems shortly
>> >after posting this here. The solution was simple: Do a SQL to round
>> >all values to 10 digits. I don't know what the max number of digits in
>> >a float is that Access can handle, but 10 are ok for me.
>> >
>> >Again thanks for your help.
>> >
>> >Markus.
>> >
>> >Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<l07b4014b4o45p7j6k15a5q4cai1ggcn1l@4ax.com>. ..
>> >> On 2 Mar 2004 23:23:58 -0800, markus_fried@gmx.de wrote:
>> >>
>> >> Access doesn't handle certain data types well in linked tables, specifically,
>> >> floating point numbers and date/time values that can contain milliseconds.
>> >> Access uses a where clause to compare the existing data with the pre-edit
>> >> data, and this can fail due to rounding error when converting to text and back
>> >> again since floating point fractional numbers may not have an exact decimal
>> >> representation and Access doesn't understand times with increments smaller
>> >> than 1 second.
>> >>
>> >> Mostly, if you try to use Number, Decimal, or Money types rather than Real or
>> >> Float types, and avoid anything that could put milliseconds into a time value,
>> >> Access is happy.[/color][/color][/color]

re: Write Conflict: This record has been changed ...

All the values in the oracle db had been inserted manually over time
by typing them in. Then we copied some of them using a little tool,
which read em in and inserted new entries. Those new entries produced
the errors. I suppose that the copy process (using java BigDecimal /
hibernate percistency framework) messed with the accuracy of the
values somehow and Access got into troubles....


Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<hplg40913pj0cilkpneafra3ptiurbkpmp@4ax.com>. ..[color=blue]
> Hmm, if they were alreasy NUMBER, I'm surprised that was the problem. I've
> only seen the problem when a floating point type was used on the server.
>
> On 4 Mar 2004 23:11:51 -0800, markus_fried@gmx.de wrote:
>[color=green]
> >We already use the Oracle NUMBER (sorry for using the term 'float')
> >data type (the Oracle tables are linked via ODBC into Access) - so I
> >don't know what I should change here.....
> >
> >
> >
> >Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<b7rd40dgjed8ddut52of0qpjed8642ncfo@4ax.com>. ..[color=darkred]
> >> Well, that's a partial solution, but not perfect. See, the problem is that
> >> the rounded decimal values won't all convert perfectly into binary fractions.
> >> For the most part, they will all convert the same kind of wrong, but not
> >> necessarily, so you can still have problems. It's better to change the data
> >> type to a NUMBER or DECIMAL type with a fixed precision.
> >>
> >> On 3 Mar 2004 23:20:57 -0800, markus_fried@gmx.de wrote:
> >>
> >> >Hi,
> >> >
> >> >thanks for your help - I found out about the float problems shortly
> >> >after posting this here. The solution was simple: Do a SQL to round
> >> >all values to 10 digits. I don't know what the max number of digits in
> >> >a float is that Access can handle, but 10 are ok for me.
> >> >
> >> >Again thanks for your help.
> >> >
> >> >Markus.
> >> >
> >> >Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<l07b4014b4o45p7j6k15a5q4cai1ggcn1l@4ax.com>. ..
> >> >> On 2 Mar 2004 23:23:58 -0800, markus_fried@gmx.de wrote:
> >> >>
> >> >> Access doesn't handle certain data types well in linked tables, specifically,
> >> >> floating point numbers and date/time values that can contain milliseconds.
> >> >> Access uses a where clause to compare the existing data with the pre-edit
> >> >> data, and this can fail due to rounding error when converting to text and back
> >> >> again since floating point fractional numbers may not have an exact decimal
> >> >> representation and Access doesn't understand times with increments smaller
> >> >> than 1 second.
> >> >>
> >> >> Mostly, if you try to use Number, Decimal, or Money types rather than Real or
> >> >> Float types, and avoid anything that could put milliseconds into a time value,
> >> >> Access is happy.[/color][/color][/color]