在 Access / SQL 中编辑记录问题（写入冲突）

Editing Record issues in Access / SQL (Write Conflict)

提问by aSystemOverload

a problem has come up after a SQL DB I used was migrated to a new server. Now when trying to edit a record in Access (form or table), it says: WRITE CONFLICT: This record has been changed by another user since you started editing it...

将我使用的 SQL DB 迁移到新服务器后出现问题。现在，当尝试在 Access（表单或表格）中编辑记录时，它说：WRITE CONFLICT: This record has been changed by another user since you started editing it...

Are there any non obvious reasons for this. There is noone else using the server, I've disabled any triggers on the Table. I've just found that it is something to do with NULLs as records that have none are ok, but some rows which have NULLs are not. Could it be to do with indexes? If it is relevant, I have recently started BULK uploading daily, rather than doing it one at a time using INSERT INTO from Access.

是否有任何不明显的原因。没有其他人使用服务器，我已禁用表上的任何触发器。我刚刚发现这与 NULL 值有关，因为没有记录的记录是可以的，但某些具有 NULL 的行则不然。会不会跟索引有关？如果是相关的，我最近开始每天批量上传，而不是使用从 Access 中的 INSERT INTO 一次上传一次。

回答by Olivier Jacot-Descombes

Possible problems:

可能的问题：

1 Concurrent edits

1 并发编辑

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.

原因可能是相关记录已在您正在编辑的表单中打开。如果您在编辑会话期间以编程方式更改记录，然后尝试关闭表单（并因此尝试保存记录），则访问表明该记录已被其他人更改。

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

2 缺少主键或时间戳

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

确保 SQL-Server 表具有主键和时间戳列。

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).

时间戳列可帮助 Access 确定自上次选择记录以来是否已编辑该记录。如果没有时间戳可用，Access 通过检查所有字段来完成此操作。如果没有时间戳列，这可能不适用于空条目（请参阅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.)

添加时间戳列后别忘了刷新access中的表链接，否则Access看不到。（注意：Microsoft 的升迁向导在将 Access 表转换为 SQL-Server 表时会创建时间戳列。）

---

---

3 Null bits issue

3 空位问题

According to @AlbertD.Kallal this could be a null bits issue described here: KB280730. If you are using bit fields, set their default value to 0and replace any NULLs entered before by 0. I usually use a BIT DEFAULT 0 NOT NULLfor Boolean fields as it most closely matches the idea of a Boolean.

根据@AlbertD.Kallal，这可能是此处描述的空位问题：KB280730。如果您使用位字段，请将其默认值设置为0并替换之前输入的任何 NULL 0。我通常使用 a BIT DEFAULT 0 NOT NULLfor Boolean 字段，因为它最接近 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.

知识库文章说使用 *.adp 而不是 *.mdb；但是，Microsoft 在 Access 2013 中停止了对 Access Data Projects (ADP) 的支持。

回答by BratPAQ

Had this problem, same as the original poster. Even on edit directly using no form. The problem is on bit fields, If your field is Null, it converts Null to 0 when you access the record, then you make changes which this time is the 2nd change. So the 2 changes conflicts. I followed Olivier's suggestion:

有这个问题，和原来的海报一样。即使直接使用不使用表单进行编辑。问题出在位字段上，如果您的字段为 Null，则在您访问记录时将 Null 转换为 0，然后您进行更改，这次是第二次更改。所以这 2 个变化冲突了。我遵循了奥利维尔的建议：

"Make sure the table has a primary key as well as a timestamp column."

“确保该表有一个主键和一个时间戳列。”

And it solved the problem.

它解决了这个问题。

回答by Spring

I have seen a similar situation with MS Access 2003 (and prior) when linked to MS SQL Sever 2000 (and prior). In my case I found that the issue to be the bit fields in MS SQL Server database tables - bit fields do not allow null values. When I would add a record to a table linked via the MS Access 2003 the database window an error would be returned unless I specifically set the bit field to True or False. To remedy, I changed any MS SQL Server datatables so that any bit field defaulted to either 0 value or 1. Once I did that I was able to add/edit data to the linked table via MS Access.

当链接到 MS SQL Sever 2000（和更早版本）时，我在 MS Access 2003（和更早版本）中看到了类似的情况。就我而言，我发现问题在于 MS SQL Server 数据库表中的位字段 - 位字段不允许空值。当我将记录添加到通过 MS Access 2003 链接的表时，数据库窗口将返回错误，除非我专门将位字段设置为 True 或 False。为了补救，我更改了任何 MS SQL Server 数据表，以便任何位字段默认为 0 值或 1。一旦我这样做了，我就能够通过 MS Access 向链接表添加/编辑数据。

回答by user607237

I found the problem due to the conflict between Jet/Access boolean and SQL Server bit fields.

由于 Jet/Access boolean 和 SQL Server 位字段之间的冲突，我发现了这个问题。

Described here under pitfall #4 https://blogs.office.com/2012/02/17/five-common-pitfalls-when-upgrading-access-to-sql-server/

此处在陷阱 #4 下描述 https://blogs.office.com/2012/02/17/five-common-pitfalls-when-upgrading-access-to-sql-server/

I wrote an SQL script to alter all bit fields to NOT NULL and provide a default - zero in my case.

我编写了一个 SQL 脚本来将所有位字段更改为 NOT NULL 并提供默认值 - 在我的情况下为零。

Just execute this in SQL Server Management Studio and paste the results into a fresh query window and run them - its hardly worth putting this in a cursor and executing it.

只需在 SQL Server Management Studio 中执行此操作并将结果粘贴到新的查询窗口中并运行它们 - 将其放入游标并执行它几乎不值得。

SELECT
    'UPDATE [' + o.name + '] SET [' + c.name + '] = ISNULL([' + c.name + '], 0);' + 
    'ALTER TABLE [' + o.name + '] ALTER COLUMN [' + c.name + '] BIT NOT NULL;' + 
    'ALTER TABLE [' + o.name + '] ADD  CONSTRAINT [DF_' + o.name + '_' + c.name + '] DEFAULT ((0)) FOR [' + c.name + ']'
FROM
    sys.columns c
INNER JOIN sys.objects o
ON  o.object_id = c.object_id
WHERE
    c.system_type_id = 104
    AND o.is_ms_shipped = 0;

回答by Hip Hip Array

This is a bug with Microsoft

这是微软的一个错误

To work around this problem, use one of the following methods:

要解决此问题，请使用以下方法之一：

• Update the form that is based on the multi-table view On the first occurrence of the error message that is mentioned in the "Symptoms" section, you must click either Copy to Clipboard or Drop Changes in the Write Conflict dialog box. To avoid the repeated occurrence of the error message that is mentioned in the "Symptoms"
  section, you must update the recordset in the form before you edit
  the same record again. Notes To update the form in Access 2003 or in Access 2002, click Refresh on the Records menu. To update the form in Access 2007, click Refresh All in the Records group on the Home tab.

• Use a main form with a linked subform To avoid the repeated occurrence of the error message that is mentioned in the "Symptoms" section, you can use a main form with a
  linked subform to enter data in the related tables. You can enter
  records in both tables from one location without using a form that is based on the multi-table view. To create a main form with a linked subform, follow these steps:

  Create a new form that is based on the related (child) table that is used in the multi-table view. Include the required fields on the form. Save the form, and then close the form. Create a new form that is based on the primary table that is used in the multi-table view. Include the required fields on the
  form. In the Database window, add the form that you saved in step 2 to the main form.

  This creates a subform. Set the Link Child Fields property and the Link Master Fields property of the subform to the name of the field or fields that are
  used to link the tables.

• 更新基于多表视图的窗体 在第一次出现“症状”部分中提到的错误消息时，您必须在“写入冲突”对话框中单击“复制到剪贴板”或“删除更改”。为避免重复出现“症状”
  部分中提到的错误消息，您必须在
  再次编辑同一记录之前更新表单中的记录集。备注 若要更新 Access 2003 或 Access 2002 中的表单，请单击“记录”菜单上的“刷新”。若要更新 Access 2007 中的表单，请单击“主页”选项卡上“记录”组中的“全部刷新”。

• 使用带有链接子窗体的主窗体 为避免重复出现“症状”部分中提到的错误消息，您可以使用带有
  链接子窗体的主窗体在相关表中输入数据。您可以
  从一个位置在两个表中输入记录，而无需使用基于多表视图的表单。要使用链接的子表单创建主表单，请执行以下步骤：

  创建一个基于在多表视图中使用的相关（子）表的新表单。在表格中包含必填字段。保存窗体，然后关闭窗体。创建一个基于多表视图中使用的主表的新表单。在
  表格中包含必填字段。在“数据库”窗口中，将您在步骤 2 中保存的表单添加到主表单中。

  这将创建一个子表单。将子窗体的链接子字段属性和链接主字段属性设置为
  用于链接表的一个或多个字段的名称。

Methods from work around taken from microsoft support

来自微软支持的解决方法

回答by KWells

I have experienced both of the causes detailed above: Directly changing data in a table that is currently bound to a form AND having a 'bit' type field in SQL Server that does not have the Default Value set to '0' (zero).

我遇到了上面详述的两个原因：直接更改当前绑定到表单的表中的数据，并且在 SQL Server 中具有“位”类型字段，该字段的默认值未设置为“0”（零）。

The only way I have been able to get around the latter issue is to add the default value of zero to the bit field AND run an update query to set all current values to zero.

我能够解决后一个问题的唯一方法是将默认值零添加到位字段并运行更新查询以将所有当前值设置为零。

In order to get around the former error, I have had to be inventive. Sometimes I can change the order of the VBA statements and move Refresh or Requery to a different location, thus preventing the error message. In most cases, however, what I do is DIM a String variable in the Subroutine where I call the direct table update. BEFORE I call the update, I set this String variable to the value of the Recordsource behind the bound form, thus capturing the exact SQL statement being used at the time. Then, I set the form's Recordsource to an empty string ("") in order to disconnect it from the data. Then, I perform the data update. Then, I set the form's Recordsource back to the value saved in the String variable, reestablishing the binding and allowing it to pick up the new value(s) in the table. If there is one or more subforms contained within this form, then the "Link" fields need to handled in a similar manner as the Recordsource. When the Recordsource is set to an empty string, you may see #Name in the now-unbound fields. What I do is simply set the Visible property to False at the highest possible level (Detail section, Subform, etc.) during the time when the Recordsource is empty, hiding the #Name values from the user. Setting the Recordsource to an empty string is my go-to solution when a coding change can't be found. I am wondering, though, if my design skills are lacking and there is a way to completely avoid the issue altogether?

为了解决前一个错误，我必须要有创造力。有时我可以更改 VBA 语句的顺序并将 Refresh 或 Requery 移动到不同的位置，从而防止出现错误消息。然而，在大多数情况下，我所做的是在我调用直接表更新的子例程中 DIM 一个字符串变量。在调用更新之前，我将此 String 变量设置为绑定表单后面的 Recordsource 的值，从而捕获当时正在使用的确切 SQL 语句。然后，我将表单的 Recordsource 设置为空字符串 ("")，以便将其与数据断开连接。然后，我执行数据更新。然后，我将表单的 Recordsource 设置回保存在 String 变量中的值，重新建立绑定并允许它获取表中的新值。如果此表单中包含一个或多个子表单，则需要以与记录源类似的方式处理“链接”字段。当 Recordsource 设置为空字符串时，您可能会在现在未绑定的字段中看到 #Name。我所做的只是在 Recordsource 为空时，在尽可能高的级别（详细信息部分、子窗体等）将 Visible 属性设置为 False，从而对用户隐藏 #Name 值。当找不到编码更改时，将 Recordsource 设置为空字符串是我的首选解决方案。不过，我想知道，如果我的设计技能不足，有没有办法完全避免这个问题？您可能会在现在未绑定的字段中看到 #Name。我所做的只是在 Recordsource 为空时，在尽可能高的级别（详细信息部分、子窗体等）将 Visible 属性设置为 False，从而对用户隐藏 #Name 值。当找不到编码更改时，将 Recordsource 设置为空字符串是我的首选解决方案。不过，我想知道，如果我的设计技能不足，有没有办法完全避免这个问题？您可能会在现在未绑定的字段中看到 #Name。我所做的只是在 Recordsource 为空时，在尽可能高的级别（详细信息部分、子窗体等）将 Visible 属性设置为 False，从而对用户隐藏 #Name 值。当找不到编码更改时，将 Recordsource 设置为空字符串是我的首选解决方案。不过，我想知道，如果我的设计技能不足，有没有办法完全避免这个问题？

One final thought on addressing the error message: Instead of calling a routine to directly update the data in the table table, I find a way to update the data via the form instead, by adding a bound control to the form and updating the data in that so that the form data and the table data do not become out of sync.

关于解决错误消息的最后一个想法：我没有调用例程来直接更新表表中的数据，而是找到了一种通过表单更新数据的方法，方法是向表单添加绑定控件并更新表中的数据这样表单数据和表数据不会变得不同步。

回答by greeny129

If you are using linked tables, ensure you have updated these and retry before doing anything else.

如果您正在使用链接表，请确保您已更新这些并在执行任何其他操作之前重试。

I thought I had updated them but hadn't, turns out someone had updated the form validation and SQL tables to allow 150 chars, but hadn't refreshed the linked table hence access only saw 50 char allowed - Boom Write conflict

我以为我已经更新了它们但没有，结果有人更新了表单验证和 SQL 表以允许 150 个字符，但没有刷新链接表因此访问只允许看到 50 个字符 - Boom 写入冲突

Not sure this is the most appropriate error for the scenario, but hey, most of the interesting issues are never flagged appropriately in any microsoft software!

不确定这是最适合该场景的错误，但是，大多数有趣的问题从未在任何 Microsoft 软件中正确标记！

回答by pperez

I′m using this workaround and it has worked for me: Front end: Ms Access Backend: Mysql

我正在使用这个解决方法，它对我有用：前端：Ms Access 后端：Mysql

On the Before update event of a given field:

在给定字段的 Before update 事件上：

Private Sub tbl_comuna_id_comuna_BeforeUpdate(Cancel As Integer)

If Me.tbl_comuna_id_comuna.OldValue = Me.tbl_comuna_id_comuna.Value Then
Cancel = True
Undo
End If
End Sub

回答by Klaus Oberdalhoff

I just had very havy write-conflict problems (Acc2013 32bit, SQL Srv2017 expr) with a rather "heavy loaded" Split-Form. For me - at last - was the solution to get rid of the write-conflict problems to simply

我刚刚遇到了非常严重的写入冲突问题（Acc2013 32 位，SQL Srv2017 expr），并且使用了相当“重载”的拆分形式。对我来说 - 最后 - 是摆脱写冲突问题的解决方案，简单地

SET THE AcSplitFormDatasheet to READ-ONLY !!! (I haven't a clue why it was read-write anyway i must have set it by fault...)

将 AcSplitFormDatasheet 设置为只读！！！（无论如何我都不知道为什么它是读写的，我一定是错误地设置了它......）

It did nearly cost me a whole week to find that out.

我几乎花了整整一周的时间才发现这一点。

回答by Stuart

In order to get over this problem. I created VBA to change another field in the same row. So I created a separate field which adds 1 to the contents when I try to close the form. This solved the issue.

为了克服这个问题。我创建了 VBA 来更改同一行中的另一个字段。所以我创建了一个单独的字段，当我尝试关闭表单时，它会向内容添加 1。这解决了这个问题。