Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 278696 - Last Review: January 29, 2007 - Revision: 3.1
ACC97: Write Conflict Error When You Try to Update Records in a Linked SQL Server Table
This article was previously published under Q278696
Advanced: Requires expert coding, interoperability, and multiuser skills.
For a Microsoft Access 2000 version of this article, see
280730Â
(http://kbalertz.com/Feedback.aspx?kbNumber=280730/EN-US/
)
.
SYMPTOMS
You receive the following write conflict error when you try to update records in a linked SQL Server table:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
You are then given the following options:
Save Record,
Copy to Clipboard, or
Drop Changes.
CAUSE
Access is creating Null bit fields, which causes a type mismatch.
RESOLUTION
To resolve this problem, do one of the following:
- Using SQL Server, open the table in Design view, and assign a default value of 0 (that is, zero) on all bit fields.
NOTE: With this option, you must update records entered before this change was made. See the next item for more information.
- Using SQL Server, run an Update Transact-SQL statement against the table, and set all bit fields that have a value of Null to a new value of 0 or 1.
- Using SQL Server, change the bit fields to some other data type, such as int or tinyint.
- Using SQL Server, add a timestamp field to the table.
MORE INFORMATION
Steps to Reproduce Behavior
The following steps assume that you have an understanding of how to create tables in SQL Server, and that you are familiar with certain SQL Server tools such as Enterprise Manager.
Also assumed is that you are aware of how to create File, User, and System Data Source Names (DSN), and how to use a DSN to link a table to a Microsoft Access 97 database.
- In Microsoft SQL Server 7.0 or later, add a new table to the Pubs sample database, with the following table structure:
Collapse this tableExpand this table
| Column Name | Data Type | Length | Allow Nulls |
|---|
| fldID | int | 4 | <unchecked> |
| fldBit | bit | 1 | <checked> |
| fldDescrip | varchar | 50 | <checked> |
- Make the following Column property assignments to the fldID:
Identity: Yes
Identity Seed: 1
Identity Increment: 1
- Set the fldID field as the primary key, and then close and save the table as Table1.
- Insert the following records into the Table1 table:
Collapse this tableExpand this table
| fldID | fldBit | fldDescrip |
|---|
| 1 | 1 | Record #1 |
| 2 | <NULL> | Record #2 |
| 3 | 0 | Record #3 |
| 4 | 1 | Record #4 |
- Close the table, and then create a User DSN that points to your SQL Server.
- Open any Access 97 database, and then link Table1 from the Pubs database into your database.
- Change the description of Record #1 to Record #0. Note that you can save your change.
- Try to change the description of Record #2 to Record #1. Note that you receive an error when you try to save your change.
APPLIES TO
- Microsoft Access 97 Standard Edition
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please