Write conflict

[Mark A. Sam]

Hello,

I linked an SQL Database table from a remote server over the internet, via
ODBC.

I can't make changes from Access. I get a write conflict message with the
only options being to Copy to Clipboard or Drop Changes. The save option is
disabled.

I am able to create a new record from Access, but if I leave the record and
return to it, I get the came issue.

I tried a DAO method from an Access form button and was not able to edit. I
get runtime error 3197:The Microsoft database engine stopped the process
becuase you and another user are attempting to change the same data at the
same time.

I am however able to make changes via the Enterprise Manager this is also
how I created the table. There is no other user on the system and the
problem occurs with the Enterprise Manager open or closed. So there are no
real user conflicts.

Any help in resolving this is appreciated.

God Bless,

Mark A. Sam

 

[Rick Brandt]

Hello,

I linked an SQL Database table from a remote server over the
internet, via ODBC.

I can't make changes from Access. I get a write conflict message
with the only options being to Copy to Clipboard or Drop Changes.
The save option is disabled.

I am able to create a new record from Access, but if I leave the
record and return to it, I get the came issue.

I tried a DAO method from an Access form button and was not able to
edit. I get runtime error 3197:The Microsoft database engine stopped
the process becuase you and another user are attempting to change the
same data at the same time.

I am however able to make changes via the Enterprise Manager this is
also how I created the table. There is no other user on the system
and the problem occurs with the Enterprise Manager open or closed.
So there are no real user conflicts.

Usually resolved by adding a Timestamp column to the SQL Server table.

The Timestamp is a special data type that is updated to a database-unique
value whenever a row is edited in the table. When this column exists Access
will compare the Timestamp value in the table to the Timestamp value in its
buffer to determine if the record has been changed by another process since
local editing began. When a Timestamp does not exist Access will compare
the value of every field in the table to every field in the buffer to see if
any of them are changed. With certain data types (decimal for one) Access
can have a problem with evaluating the value accurately enough and it might
"think" that a value has been changed when it really hasn't (thus the
error).

The above being the case you won't see this in every table linked from a SQL
Server, but it's not a bad practice to include a Timestamp column in all
tables that will be linked to from Access.

 

[Mark A. Sam]

Thank Rick.. that fixed the problem, however now I can't edit a field which
contains data. I can add data to a blank field, or I can delete the
existing date, but I can't add additional data to a field which contains any
characters. This may not be a problem, becuase most of the changes or
update will be done though programming, but if there is a resolution, I'd
like to fix it.

God Bless,

Mark

 

[Sue Hoegemeier]

The problem and need for timestamps actually occurs with
approximate data types such as float or real. The problem
you are seeing can occur when you have a float data type and
no timestamp OR you have indexes with float or datetime data
type. Refer to the following article:
ACC: Operation Stops When Editing Attached SQL Tables
http://support.microsoft.com/?id=96897

-Sue

 

[Rick Brandt]

Thank Rick.. that fixed the problem, however now I can't edit a field
which contains data. I can add data to a blank field, or I can
delete the existing date, but I can't add additional data to a field
which contains any characters. This may not be a problem, becuase
most of the changes or update will be done though programming, but if
there is a resolution, I'd like to fix it.

Is the field defined as VarChar on the server or Char? If Char it will store
trailing blanks and your input attempts have to be in type-over mode rather than
insert (because the field is always full). You don't see this when replacing
the entire field (as when it is empty) because you usually tab in (the entire
field contents are selected) and then you start typing. If you use your mouse
to click to the end of existing text and aren't in type-over mode the trailing
blanks prevent your additional characters.

 

[Mark A. Sam]

Rick and Sue,

The problem seems to have disappeared. I don't know if it occured from
Access, Enterprise manager or both, at the time I posted this, but it does't
seem to be an issue now. I do appreciated the information about VarChar,
becuase I don't like those Char fields.

God Bless,

Mark