MS SQL Server ODBC - Write conflict

Author

Write conflict

Mark A. Sam

2005-05-24, 9:23 am

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

2005-05-24, 9:23 am

Mark A. Sam wrote:
> 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.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Mark A. Sam

2005-05-24, 9:23 am

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

"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
news:yRFke.2057$TJ2.250@newssvr11.news.prodigy.com...
> Mark A. Sam wrote:
>
> 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.
>
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
>

Sue Hoegemeier

2005-05-24, 8:23 pm

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

On Tue, 24 May 2005 09:32:49 -0400, "Mark A. Sam"
<msam@Plan-It-Earth.Net> wrote:

>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
>
>
>"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
>news:yRFke.2057$TJ2.250@newssvr11.news.prodigy.com...
>Access
>its
>since
>if
>might
>SQL
>

Rick Brandt

2005-05-25, 7:23 am

Mark A. Sam wrote:
> 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.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Mark A. Sam

2005-05-25, 11:23 am

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

"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
news:yJZke.973$4N2.158@newssvr30.news.prodigy.com...
> Mark A. Sam wrote:
>
> 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.
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
>

Sponsored Links