Write conflict on SQL 2000 ODBC linked table

[Rico]

Hello,

I have an Access 2002 front end that is connected to an SQL Server 2000 back
end. The ODBC links connected to the front end are DSNless and have
indexes, so they are updatable. When I try to modify information in the
table (accessing the table directly through the database window) I get a
write conflict ("This record has been changed by another user since you
started...etc..etc") with the options of Copy to Clipboard and Drop Changes
(Save Record is greyed out). I am the only user in the database, there are
no triggers and this occurs with or without default values in the individual
columns in the back end table (FWIW there are only '0' defaults on numeric
and bit fields).

I've done a little googling, but most of my results have to do with VB code
or timestamp defaults, and nothing quite as strange as I'm encountering.

Any ideas?

Thanks!
Rick

 

[Guest]

This is why you should have "timestamp" fields on
all of your tables. If you have the "timestamp" type
field (which is not actually a timestamp), Access
and SQL Server check the timestamp field to see
if the data has changed. They don't check the
actual data, so they don't get confused by date/time
fields and floating point numbers, which get changed
accidentally just in the conversion between Access
and SQL Server.

(david)

 

[Dan Guzman]

You might try a SQL Profiler trace to see the values being passed to SQL
Server for the optimistic concurrency check. Maybe that will indicate the
problem area. Do you have any float/real data types in the table?

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

 

[Albert D. Kallal]

two things:

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
Server Table
http://support.microsoft.com/?id=280730

And, make sure that ALL tables have a primary key and ALSO HAVE A timestamp
field. While the above "null bits" might fix your write conflict, you will
want a timestamp field. I find problems with sub-forms refreshing if you
don't have a timestamp field......

 

[Rico]

Perfect! The timestamp did the trick.

Thanks all!

Rick

 

[a a r o n . k e m p f]

well that might work ok for you until Access chokes on another
datatype that isn't supported in jet.

you should just move everything to ADP, it's 100 times easier to deal
with

 

[Rico]

If only the clients had to cater to us, what a wonderful world it would be.


message
well that might work ok for you until Access chokes on another
datatype that isn't supported in jet.

you should just move everything to ADP, it's 100 times easier to deal
with

 

[Tony Toews [MVP]]

"Rico" <[email protected]> wrote:

If only the clients had to cater to us, what a wonderful world it would be.

Aaron is monomaniacal when it comes to ADPs.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/