473,728 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
Nov 12 '05 #1
8 12003
On 2 Mar 2004 23:23:58 -0800, ma**********@gm x.de wrote:
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.


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.
Nov 12 '05 #2
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.
Nov 12 '05 #3
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
<no****@nospam. nospam> wrote:
Access uses a where clause to compare the existing data with the pre-edit
data


Nov 12 '05 #4
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 <no****@nospam. nospam> wrote in message news:<l0******* *************** **********@4ax. com>...
On 2 Mar 2004 23:23:58 -0800, ma**********@gm x.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.

Nov 12 '05 #5
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, ma**********@gm x.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 <no****@nospam. nospam> wrote in message news:<l0******* *************** **********@4ax. com>...
On 2 Mar 2004 23:23:58 -0800, ma**********@gm x.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.


Nov 12 '05 #6
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 <no****@nospam. nospam> wrote in message news:<b7******* *************** **********@4ax. com>...
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, ma**********@gm x.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 <no****@nospam. nospam> wrote in message news:<l0******* *************** **********@4ax. com>...
On 2 Mar 2004 23:23:58 -0800, ma**********@gm x.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.

Nov 12 '05 #7
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, ma**********@gm x.de wrote:
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 <no****@nospam. nospam> wrote in message news:<b7******* *************** **********@4ax. com>...
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, ma**********@gm x.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 <no****@nospam. nospam> wrote in message news:<l0******* *************** **********@4ax. com>...
>> On 2 Mar 2004 23:23:58 -0800, ma**********@gm x.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.


Nov 12 '05 #8
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 <no****@nospam. nospam> wrote in message news:<hp******* *************** **********@4ax. com>...
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, ma**********@gm x.de wrote:
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 <no****@nospam. nospam> wrote in message news:<b7******* *************** **********@4ax. com>...
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, ma**********@gm x.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 <no****@nospam. nospam> wrote in message news:<l0******* *************** **********@4ax. com>...
>> On 2 Mar 2004 23:23:58 -0800, ma**********@gm x.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.

Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
94549
by: Max | last post by:
Hi, I have SqlServer 2000 as back end and Access 2000 as front-end. All tables from Sqlserver are linked to Access 2000. I am having write conflict problem with one of my form which is bound to a query which consist of let say tableA , TableB and TableC (all are linked tables from SqlServer. While the form is open I am modifying TableA through code on Form_AfterUpdate Event and getting the following message.
1
1878
by: Simon | last post by:
Dear reader, In case I work with two Forms and using the same Table but with other fields in the Forms, I have the following experience. After I change in one of the two Forms a field I can't move to another record in the other form because of a record locking problem. How can I avoid the popup window witch telling me:
2
15418
by: RC | last post by:
I am getting the following error message. "Write Conflict this record has been changed by another user since you started edting it. If you save the record, you will overwrite the changes...." I have an Access 2002 database running on a single PC, the front and backend have not been separated yet. I have a form where I use a text box to jump from record to record. The form has about 12 bound text boxes on it. The table that is the...
0
2335
by: Smriti Dev | last post by:
Hi, I have a ms access table table which is linked to a mysql database table. I have used a ms access form to edit records. However, when I attempt to edit a record I get a write conflict error in ms access. I am using MS ACCESS 2002. The error is 'the record has been changed by another user since you started editi it. If you save the record, you will overwrite the changes the other user made'. The Save record button is greyed out so I...
5
4730
by: Simon | last post by:
Dear reader, I have two Forms they are both working with dada from the same tables. By typing in some changes in Form-B the changes are also visible in Form-A. There is no record lock set.
1
2447
by: lorirobn | last post by:
Hi, I have a query that I have been using as a record source for a form with no problems. I just created a new "addnew" form, and added 20 records to the table with this form. The problem I now have is I cannot modify data on these new records using my query. The query's sql is: SELECT tblSpaceUse.* FROM tblSpaceUse; The error I get is: "Write Conflict -
8
12950
by: christianlott1 | last post by:
After searching the group and the net, I just can't believe after all this work I'm going to have to sit through three Write Conflict messages when I want to roll back and delete a record. The action rolls back a new hire record, deleting it from a joining table between employee and position - tblPosEmpRecord. It's a many to many join. The new hire action updates the employee with a new position and logs
1
4026
by: S.Dickson | last post by:
I have a database with access as front end and Mysql as back end. I am gettting the following 'Write Conflict' Error. when i am on my order form, This form does has a subform where i enter all the products that are ordered. Could any one let me know what could cause this problem. I am the only one using the database as its still in the build stage Thanks
6
4304
by: jpatchak | last post by:
Hello, I have a main form with one subform. I have a command button on the main form to delete the current record. Below is my code. The tables on which the main form and subform are based cascade deletions and updates, so getting rid of the record in the table for the main form should delete any corresponding records in the table for the subform. Everything appears to be working correctly in this code from the standpoint of the tables. ...
4
2104
by: Rolrox | last post by:
I've discovered that in Sybase, I had a field/column whose datatype was defined as "bit" which also permitted NULL values. When I changed this to smallint, the write conflict went away.
0
8904
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
9198
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8133
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6718
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4524
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4793
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3236
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2668
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.