Error when trying to update SQL Server from MS Access

[Del]

We have a MS SQL Server 2000 application for our shipping system. We
have created a front end application using MS Access for our order
analyst. The front end application allows the analyst to review and
update orders before they are released to the warehouse. We are in the
test phase of this project and have run into a couple problems that we
just can not figure out.

During the testing phase there is only one person working in the
system.

1) From time to time we receive an error after data has been entered
into a field, and the user exits the field. The error reads:

"The data has been changed."
"Another user edited this record and saved the changes before you
attempted to save your changes."
"Re-Edit the record>'

The data that was just entered is then deleted from the field.
Re-Entering the data and exiting the field then works fine and the
data is written to the SQL Table. This error can happen on any field
(Text Boxes, Combo Boxes, Check Boxes etc.) of the Access form at any
time.

Is this error possibly because SQL is editing the record at the same
time as the user?

2) Also from time to time the user will recieve a ODBC error. The
error is quite lengthly, but it basically says access has been denied.
Exiting the Access application and restarting it seems to help some
times but not always. We have tried using NT Authenication, and SQL
Authenication. The SQL Authenication seems to work better in that we
don't receive this error as often. All user access have been given
Read, Write, Change access to the tables.

Thanks in advance for any help on these issues.

We are using MS Access 2000, XP, 2003 and MS SQL Server 2000

Thanks again,

Del

[DC Fan]

Thoughts below:

"Del" <dt****@yahoo.com> wrote in message
news:c2**************************@posting.google.c om...

We have a MS SQL Server 2000 application for our shipping system. We
have created a front end application using MS Access for our order
analyst. The front end application allows the analyst to review and
update orders before they are released to the warehouse. We are in the
test phase of this project and have run into a couple problems that we
just can not figure out.

During the testing phase there is only one person working in the
system.

1) From time to time we receive an error after data has been entered
into a field, and the user exits the field. The error reads:

"The data has been changed."
"Another user edited this record and saved the changes before you
attempted to save your changes."
"Re-Edit the record>'

The data that was just entered is then deleted from the field.
Re-Entering the data and exiting the field then works fine and the
data is written to the SQL Table. This error can happen on any field
(Text Boxes, Combo Boxes, Check Boxes etc.) of the Access form at any
time.

Is this error possibly because SQL is editing the record at the same
time as the user?
You seem to imply that this happens on a field-by-field basis and not on a
record-by-record basis. If so, do you have code running when the data in a
any one of a specific set of controls changes? I only ask since it might
point to something funky going on.

Generally, you should examine all of the "automatic" data changes that may
be taking place such as any triggers in the SQL server that alter the data
when it is read (a n audit log trigger might do this.) Also, review and
auto-changes that Access may be doing such as auto-number functions or code.

Are there any trends on when this happens or does it appear completely
random?

I have been developing using Access for years using SQL Sever as a back end
and only occasionally do I run into such an issue. Innevitably, there is
some rogue function (or maybe a user) that is making Access and SQL server
think that the data has changed since you read the record.

2) Also from time to time the user will recieve a ODBC error. The
error is quite lengthly, but it basically says access has been denied.
Exiting the Access application and restarting it seems to help some
times but not always. We have tried using NT Authenication, and SQL
Authenication. The SQL Authenication seems to work better in that we
don't receive this error as often. All user access have been given
Read, Write, Change access to the tables.
Do you have the text of the error message?

Thanks in advance for any help on these issues.

We are using MS Access 2000, XP, 2003 and MS SQL Server 2000

Thanks again,

Del