| Groups | Blog | Home |
sql server odbc : Write conflict
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
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 [quoted text, click to view] "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message news:yRFke.2057$TJ2.250@newssvr11.news.prodigy.com... > 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 > >
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
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" [quoted text, click to view] <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... >> 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 >> >> >
[quoted text, click to view]
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
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 [quoted text, click to view] "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message news:yJZke.973$4N2.158@newssvr30.news.prodigy.com... > 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 > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |