在 Access/SQL 中编辑记录问题(写入冲突) [英] Editing Record issues in Access / SQL (Write Conflict)

问题描述

我使用的 SQL 数据库迁移到新服务器后出现问题.现在，当尝试在 Access(表单或表格)中编辑记录时，它会显示:WRITE CONFLICT:自从您开始编辑此记录以来，该记录已被其他用户更改...

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

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

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.

推荐答案

可能的问题:

1 个并发编辑

原因可能是相关记录已在您正在编辑的表单中打开.如果您在编辑会话期间以编程方式更改记录，然后尝试关闭表单(从而尝试保存记录)，则 access 表示该记录已被其他人更改(当然是您，但 Access 不知道)).

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 缺少主键或时间戳

确保 SQL-Server 表有一个主键和一个时间戳列.

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

时间戳列可帮助 Access 确定自上次选择记录后是否已编辑该记录.如果没有可用的时间戳，Access 通过检查所有字段来完成此操作.如果没有时间戳列，这可能不适用于空条目(请参阅3 空位问题).

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.

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

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 个空位问题

根据@AlbertD.Kallal，这可能是此处描述的空位问题:KB280730(WayBackMachine 上的最后一张快照，原文章已删除).如果您使用位域，请将它们的默认值设置为 0 并用 0 替换之前输入的任何 NULL.我通常对布尔字段使用 BIT DEFAULT 0 NOT NULL，因为它最接近布尔值的概念.

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.

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

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.

这篇关于在 Access/SQL 中编辑记录问题(写入冲突)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！