reprosser
asked on
Write conflict on new records since switching to SQL Server backend
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.
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.
post the sql that is being attempted and give further information on the error message / message & numbers received
please.
please.
ASKER
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.
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.
ASKER
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".
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".
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?
"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?
You relinked the tables in access after you changed the structure in SQL Server right?
ASKER
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.
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.
ASKER
Almost all my Access Primary keys were auto increment ID fields.
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....
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....
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
thanks
rick
I have the same problem and I search, search....
Thanks for this issue. now, that's work fine.
Thanks for this issue. now, that's work fine.
Excellent solution. Thanks
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
Best of luck
Kfalandays
Hi There we have had a problem today with write problem and did the above and perfect working fine.
Cheers
Steve
Cheers
Steve
Thank you! Thank you! Thank you!. Been pulling my hair out for days on this one!
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
I was really going nuts with this one....
This solution was perfect. Thanks! -Dan
please.