Solved

Write conflict on new records since switching to SQL Server backend

Posted on 2004-04-02
18
3,360 Views
Last Modified: 2012-05-04
I have a MS Access 2K front end and just switched to SQL Server 7 backend.

I imported the data from the prevous MSAccess backend and I had to set the primary keys and Identity fields in SQL Server.

I can open the front end and edit any old data, but if I create new data, I get the "Write Conflict" error message and cannot save the changes. (I am the only one trying to work in these databases)
The only options are drop changes and save to clipboard.

I also get the error if I try to delete a record. May be Referential Integrety issue ?

I am linking tables (ADO) as SQL authenticated user. I expect there may be a permissions or property setting that needs to be changed in the database or tables.
0
Comment
Question by:reprosser
  • 6
  • 3
  • 2
  • +6
18 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10743781
post the sql that is being attempted and give further information on the error message / message & numbers received
please.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10743782
post the sql that is being attempted and give further information on the error message / message & numbers received
please.
0
 

Author Comment

by:reprosser
ID: 10744120
I am using bound forms in Access, so it is a matter of filling in or changing data on the form, and then closing the form.

Let me clarify that I can create a new record - but then if I go back to make changes to the record - I get the write conflict.
0
 

Author Comment

by:reprosser
ID: 10744211
I also have the problem if I go to the MS Acess table and try to change data in a new record.
I can make the changes without errors - if I go into SQL Server database.

The full error message:

"WRITE CONFLICT - 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."

The "Save record" button is greyed out, and the other buttons are "Copy to clipboard", and "Drop Changes".
0
 
LVL 34

Expert Comment

by:arbert
ID: 10744302
You do have the primary key defined correctly?  Doesn't really sound like ref-integrity errors (Access is usually pretty good with showing those errors).  Sounds like the primary key isn't defined correctly.   if you just open the linked-table in access, can you change the record without error?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10744307
"I imported the data from the prevous MSAccess backend and I had to set the primary keys and Identity fields in SQL Server."


You relinked the tables in access after you changed the structure in SQL Server right?
0
 

Author Comment

by:reprosser
ID: 10744403
The three choices for Identity in SQL were:
No
Yes
Yes (Not for replication)

I chose Yes. If the primary key was auto increment, I set Identity seed to last record ID and increment to 1.

If I open a linked table in Access, I cannot make changes in any records that have been created since the conversion to SQL. I can update all the records that were originally in the Access database and converted to SQL.

Yes, I re-link each time I open the Access front end.
0
 

Author Comment

by:reprosser
ID: 10744464
Almost all my Access Primary keys were auto increment ID fields.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10744527
Ya, still sounds like a primarykey/linked table problem.  

I would run SQL Profiler next time you change  a record and see what's going on.  See if the update statement uses the keys it should....
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:reprosser
ID: 10744548
I will see if I can figure out how to use SQL Profiler. This is first project with SQL Server, so I am not very familiar with it.

I am on the east coast and have to be out of town this weekend, so I may not be able to implement any suggestions until Monday.

Thanks for any help.
0
 
LVL 17

Accepted Solution

by:
Karen Falandays earned 250 total points
ID: 10745564
Here's the issue, reprosser, check and see if you had any yes/ no type data that went upt wo SQL. It gets converted to bit fields and causes a problem. Here is the text of the Ms KB article that addresses the issue
Karen

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL Server Table
The information in this article applies to:
·      Microsoft Access 2000
This article was previously published under Q280730
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 97 version of this article, see 278696.
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 (zero) on all bit fields.

NOTE: With this option, you must update records that were 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.
·      Use a Microsoft Access project (*.adp) instead of an Access database (*.mdb).
NOTE: If you make changes to the data types in the SQL Server tables, relink the tables in Microsoft Access.
0
 

Author Comment

by:reprosser
ID: 10757062
The Yes/No fields were the problem. I had changed the bit fields to allow nulls, but setting the default to 0 fixed the problem.

thanks
rick
0
 

Expert Comment

by:tbardin
ID: 10996742
I have the same problem and I search, search....
Thanks for this issue. now, that's work fine.

0
 

Expert Comment

by:kangfucius
ID: 14445796
Excellent solution. Thanks
0
 
LVL 17

Expert Comment

by:Karen Falandays
ID: 14446369
Hello folks, so glad that the answer is still helping others. Believe me, when I had the issue, I practically had to bring in an army to convince my dba that this was the issue!!!
Best of luck
Kfalandays
0
 

Expert Comment

by:keyparker
ID: 20939490
Hi There we have had a problem today with write problem and did the above and perfect working fine.

Cheers

Steve
0
 

Expert Comment

by:ceramicpc
ID: 23669145
Thank you! Thank you! Thank you!.  Been pulling my hair out for days on this one!
0
 

Expert Comment

by:dsg138
ID: 25504839
I had the exact same issue where only the LAST record wouldn't save.
I was really going nuts with this one....

This solution was perfect.  Thanks!  -Dan
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now