编辑Access/SQL中的记录问题(写冲突) [英] Editing Record issues in Access / SQL (Write Conflict)
问题描述
将我使用的SQL DB迁移到新服务器后出现了问题.现在，当尝试在Access(表单或表)中编辑记录时，它说:WRITE CONFLICT: This record has been changed by another user since you started editing it...
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...
是否存在任何不明显的原因.没有其他人在使用服务器，我已经禁用了Table上的所有触发器.我刚刚发现这与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表示该记录已被其他人更改.
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缺少主键或时间戳
确保SQL-Server表具有主键和时间戳列.
Make sure the SQL-Server table has a primary key as well as a timestamp column.
时间戳列可帮助Access确定自上次选择记录以来是否对其进行了编辑.如果没有可用的时间戳，Access会通过检查所有字段来做到这一点.如果没有时间戳列，这可能不适用于null条目(请参见 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将看不到它. (注意:将Access表转换为SQL-Server表时，Microsoft的升迁向导会创建时间戳列.)
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 .如果使用位字段，请将其默认值设置为0，并用0替换之前输入的所有NULL.我通常将BIT DEFAULT 0 NOT NULL用于布尔字段，因为它与布尔的概念最接近.
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 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.
这篇关于编辑Access/SQL中的记录问题(写冲突)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
