如何克服/解决本地访问表和链接访问表之间的差异
好的。快速背景： MS Access 2003，具有从 Access 97 升级的 2003/2003 格式 MDB 文件。
出于本示例的目的，有两个表。
Table 1
Asset
ID - (text 20)
ParentID - (text 20)
Other fields
AssetRels
ID - (text 20)
当记录添加到 Asset 时，ID 会添加到 AssetRels。
从 Asset.ID 到 AssetRels.ID 之间存在一种关系，通过级联更新和级联删除来强制引用完整性。
从 AssetRels.ID 到 Asset.ParentID 存在一种仅通过级联更新强制引用完整性的关系。
我在数据数据库中的资产中有 2 条记录
VACU0703200, NULL
VACU0703250, VACU0703200
，我可以进入表并将 VACU0703200 更改为 VACU0704500，并且更改将按预期传播。
如果我打开链接到数据数据库的前端数据库，并尝试相同的更改（直接在表中），我会得到“无法更新；当前已锁定”（不，没有关于“另一个会话”的信息，这就是整个错误消息）
两个数据库都设置为“默认记录锁定”的“无锁”。
显然，行/页/表级锁存在一些差异，导致级联更新无法正常工作。
有谁知道我可以在某处使用一些属性设置来阻止此错误？ 我宁愿不必删除这种关系，但否则我可能必须这样做，并在代码中处理它。
编辑：原因是该表包含备注字段。显然，通过链接表，Memo 字段溢出 4K 行大小会升级为表锁。这反过来又引发了问题。
解决方案（hackish）是防止对表单上的 ID 字段进行编辑，并添加新表单进行重命名。在打开新表单之前保存更改，并通过更新查询执行更新。
Ok. Quick background:
MS Access 2003 with 2003/2003 format MDB file upgraded from Access 97.
For the purposes of this example, there are two tables.
Table 1
Asset
ID - (text 20)
ParentID - (text 20)
Other fields
AssetRels
ID - (text 20)
When a record is added to Asset, the ID is added to AssetRels.
From Asset.ID to AssetRels.ID there is a relationship that exists that enforces referential integrity with cascade update and cascade delete.
From AssetRels.ID to Asset.ParentID there is a relationship that enforces referential integrity with cascade update only.
I have 2 records in Asset
VACU0703200, NULL
VACU0703250, VACU0703200
In the data DB, I can go into the table and change VACU0703200 to VACU0704500 and the changes propogate as expected.
If I open the front end DB that links to the data DB, and try the same change (in the table directly) I get "Could not update; currently locked" (no, nothing about 'another session', that's the whole error message)
Both databases are set to "no lock" for the "default record locking"
Obviously, there is some difference in row/page/table level locks that is preventing the cascade update from working.
Does anyone know of some property settings that I can use somewhere to stop this error?
I would prefer not to have to remove the relationship, but otherwise I might have to, and handle it in code.
Edit: Cause is that the table contains a Memo field. Apparently via the linked table, having the Memo field overflow the 4K row size escalates to a table lock. which in turn triggers the problem.
Solution (hackish) is to prevent edits to the ID field on the form, and add a new form to rename. Save changes before opening the new form, and performing the update via an update query works.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不明白为什么移动到前端并从链接表中编辑它会产生任何影响。不过，我会删除表格链接，然后重新链接。有时，有时会设置一些属性并读取链接时间，如果您转到后端数据库并更改某些表属性，通常重新链接表是一个非常好的主意。
作为一般规则，当您向 SQL Server 添加带有链接表的新列时，您通常必须执行上述操作。但是，我建议您删除前端的链接表，然后尝试重新链接，这应该可以解决此问题。
I don't see why moving to the front end and editing this from a linked table should make any difference. I would however delete the table links, and then RE link. Often sometimes some properties are set up and read a time of linking, if you go to the back end database and change some of the table properties, often it's a very good idea to RE link the tables.
As a general rule, you more often have to do the above when you add new columns with linked tables to SQL server. However, I would suggest you delete your linked tables in the front end, and try RE linking, this should fix this problem.
解决方案（hackish）是防止对表单上的 ID 字段进行编辑，并添加新表单进行重命名。在打开新表单之前保存更改，并通过更新查询执行更新。
原因是当文本溢出 4K 表行限制时发生表锁升级。
Solution (hackish) is to prevent edits to the ID field on the form, and add a new form to rename. Save changes before opening the new form, and performing the update via an update query works.
The cause is a table lock escalation that occurs when the text overflows the 4K table row limit.