> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Fixing a Persistent "Write Conflict" Error
- URL: https://nolongerset.com/write-conflict-error-datetime2-bug/
- Published: 2022-06-11T02:33:04.000Z
- Updated: 2026-05-08T12:57:06.000Z
- Description: If you are getting consistent "Write Conflict" warnings when using bound forms to update SQL Server tables with datetime fields, there may be an easy fix.
- Author: Mike Wolfe
- Tags: Debugging, SQL Server, #Import 2026-05-20 02:59

After a recent SQL Server migration from SQL Server 2012 to SQL Server 2019, my users started receiving the following error when editing one of our tables:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/06/image-24.png)

This "Write Conflict" error had never appeared prior to the migration. 

## Context

After some troubleshooting, I found that I could reliably reproduce the issue when running the 32-bit Access 2013 Runtime on my client's network, but not when debugging on my development machine (running Access 365 Version 2111 Build 16.0.14701.20206 64-bit). 

Both machines were using the same ODBC driver: "**ODBC Driver 17 for SQL Server**." 

As part of the migration, I changed the SQL Server Compatibility Level of the database from **90** (SQL Server 2005) to **150** (SQL Server 2019).

Here's the table definition for the **AcctDocs** table (*note, I use singular nouns for my table names for new development*):

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/06/image-26.png)

This table would be named `AcctDoc` if I were creating it today.

## Debugging with XEvent Profiler

To figure out what was going on, I created a couple of XEvent Profiler sessions.

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/06/image-30.png)

Note that these are not identical records, as one came from the production environment and the other from my development environment.

If you've never used XEvent Profiler or SQL Server Profiler to peak under the hood, you really should. You'll be amazed at [what's actually going on behind the scenes](https://nolongerset.com/playing-telephone-with-sql-server/).

What the above screenshot shows are two different `sp_executesql` calls that Access auto-generated when the user tried to save changes in a **bound form**. 

## Comparing the Two T-SQL Statements

There were two differences between the statements above:

|                | **WORKING** | **BROKEN** |
| -------------- | ----------- | ---------- |
| Data type      | datetime    | datetime2  |
| Time precision | 0.123       | 0.1234567  |

I extracted the T-SQL string from the broken machine and executed it in SSMS:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/06/image-29.png)

I changed the DECLARE @P5 line to...

```sql
DECLARE @P5 datetime='2018-11-07 13:57:42.197'
```

...then tried executing the UPDATE statement again. This time it succeeded, displaying, "(1 row affected)."

In other words, the reason Access returned the error is because **it appeared that some other process had changed the value of the `ScannedAt` field**.

In reality, it was Access itself that converted the datetime value to a datetime2 value before doing the comparison. Thus, it *always* looked like the data had changed.

## Working Around the Error

I never did figure out why the two versions of Access had different behavior, but I did come up with a reliable fix: **[adding a rowversion field to the table](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/bb188204%28v=sql.90%29?redirectedfrom=MSDN#supporting-concurrency-checks)**.

> Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all **UPDATE** and **DELETE** statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.

I ran the following T-SQL statement to add a new column named "odbc\_id" to my table:

```sql
-- improve ODBC linked table concurrency
--  see: https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx
ALTER TABLE dbo.AcctDocs ADD odbc_id rowversion;  
```

Here's the refreshed column listing for the table:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/06/image-27.png)

## How the Workaround Works

Now, when I save data changes to the table in my bound form, this is the T-SQL that gets generated (I added the whitespace for readability):

```sql
exec sp_executesql N'
UPDATE "dbo"."AcctDocs" 
SET "isFlagged"=@P1 
WHERE "AcctDocID" = @P2
  AND "odbc_id" = @P3
',N'
@P1 smallint,
@P2 int,
@P3 binary(8)
',
-1,
9122,
0x000000000000C48E
```

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/06/image-28.png)

In the original screenshot, there were 11 clauses in the WHERE condition–one for each field in the table at the time. That's because Access had to be sure that *none* of the other values had changed from the time the record was loaded until I attempted to save my changes.

In the new version, there are only two clauses in the WHERE condition:

1. One to identify the record based on its unique key (`AcctDocID`)
2. The other to see if the record had been UPDATEd by another process since it was loaded in the form (by making sure the `rowversion`\-typed field had not changed)

This is both more efficient than checking every field, AND it avoids the datetime2 problem. Win-win. 

Of course, I'd still love to know what's going on with the datetime2 bug....

### Referenced articles

[Playing Telephone with SQL ServerYou won’t believe how Access and SQL Server actually talk to each other. You’re going to need to see this for yourself.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/01/telephone-450639_1920.jpg)](https://nolongerset.com/playing-telephone-with-sql-server/)

### External references

[Optimizing Microsoft Office Access Applications Linked to SQL ServerMicrosoft DocsArchiveddocs![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/bb188204%28v=sql.90%29?redirectedfrom=MSDN#supporting-concurrency-checks)

*Image by [kimostrupler2002](https://pixabay.com/users/kimostrupler2002-9946412/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=4031313) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=4031313)*