Editing Record issues in Access / SQL (Write Conflict)

by

Possible problems:

1 Concurrent edits

A reason might be that the record in question has been opened in a form that you are editing. If you change the record programmatically during your editing session and then try to close the form (and thus try to save the record), access says that the record has been changed by someone else (of course it’s you, but Access doesn’t know).

Save the form before changing the record programmatically.
In the form:

'This saves the form's current record
Me.Dirty = False

'Now, make changes to the record programmatically

2 Missing primary key or timestamp

Make sure the SQL-Server table has a primary key as well as a timestamp (= rowversion) column.

The timestamp column helps Access to determine if the record has been edited since it was last selected. Access does this by inspecting all fields, if no timestamp is available. Maybe this does not work well with null entries if there is no timestamp column (see 3 Null bits issue).

The timestamp actually stores a row version number and not a time.

Don’t forget to refresh the table link in access after adding a timestamp column, otherwise Access won’t see it. (Note: Microsoft’s Upsizing Wizard creates timestamp columns when converting Access tables to SQL-Server tables.)

3 Null bits issue

According to @AlbertD.Kallal this could be a null bits issue described here: KB280730 (last snapshot on WayBackMachine, the original article was deleted). If you are using bit fields, set their default value to 0 and replace any NULLs entered before by 0. I usually use a BIT DEFAULT 0 NOT NULL for Boolean fields as it most closely matches the idea of a Boolean.

The KB article says to use an *.adp instead of a *.mdb; however, Microsoft discontinued the support for Access Data Projects (ADP) in Access 2013.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. CROSS JOIN vs INNER JOIN in SQL
  3. How to cast the DateTime to Time
  4. How can I schedule a job to run a SQL query daily?
  5. How to generate a range of numbers between two numbers?
  6. Reset identity seed after deleting records in SQL Server
  7. How to avoid the “divide by zero” error in SQL?
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. How to simulate UNPIVOT in Access?
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. How to update Identity Column in SQL Server?
  12. Common Table Expression, why semicolon?
  13. T-SQL split string based on delimiter
  14. Concatenate values based on ID
  15. SQL Server 2008: how do I grant privileges to a username?
  16. How do I find duplicates across multiple columns?
  17. Detect consecutive dates ranges using SQL
  18. Split values over multiple rows [duplicate]
  19. How to do an inner join on row number in sql server
  20. sql varchar(max) vs varchar(fix)
  21. How do I prevent a database trigger from recursing?
  22. SQL Server: should I use information_schema tables over sys tables?
  23. store arabic in SQL database
  24. Convert Date format into DD/MMM/YYYY format in SQL Server
  25. Converting Select results into Insert script – SQL Server
  26. TSQL – How to use GO inside of a BEGIN .. END block?
  27. How to combine results of two queries into a single dataset
  28. Executing SQL Server Agent Job from a stored procedure and returning job result
  29. Cumulative Total in MS Sql server [duplicate]
  30. How do I find the data directory for a SQL Server instance?

Leave a Comment