问题描述
因此，Access中的记录锁定非常糟糕.我不能使用内置的记录锁定，因为它会锁定记录的页面"而不是单个记录(我尝试更改设置以使用记录级锁定，但是它仍在锁定页面，而不仅仅是一条记录)，但是即使我可以使用该记录，也无法解决我的问题，因为直到用户开始对表单进行更改之前，记录不会锁定.
So, record-locking in Access is pretty awful. I can't use the built-in record locking because it locks a "page" of records instead of just the individual records (I've tried changing the settings for using record-level locking, but it's still locking a page instead of just one record), but even if I could get that working, it wouldn't solve my issue because the record doesn't lock until the user starts to make changes in the form.
问题是，当两个人打开相同的记录时，他们可以开始进行更改并且都保存(从而覆盖了之前的更改).更糟糕的是，表单上有一些列表框链接到其他表(键入ID)，如果它们都打开相同的记录，则它们对这些表所做的更改将被随后的所有更改覆盖.
The issue is, when two people open the same record, they can start making changes and both save (thus overwriting the earlier change). To make matters worse, there are listboxes on the form that link to other tables (keyed on an ID) and the changes they make to those tables are then overwritten by any change that comes after if they both opened the same record.
长话短说，我需要确保两个人无法同时打开同一记录(无论他们是否对其进行了任何编辑)
Long story short, I need to make sure it's impossible for two people to even open the same record at the same time (regardless of whether or not they've made any edits to it yet).
为此，我在表中添加了一个字段，该字段指示记录是否已被用户锁定.当他们打开表单时，它会在字段中设置其名称，其他尝试打开该记录的用户会收到有关其已被锁定的通知.问题是，此锁定"不是瞬时的.其他用户需要几秒钟的时间才能检测到"该记录已被锁定，因此，如果两个人尝试在大致相同的时间打开同一条记录，这将使他们俩都可以打开该记录.我已将事务应用于设置了锁的UPDATE语句，但它仍然留下一小段窗口，其中锁没有拿走"，两个人可以打开同一条记录.
To do this, I added a field to the table which indicates if a record has been locked by a user. When they open a form, it sets their name in the field and other users who try to open that record get a notification that it's already locked. The problem is, this "lock" isn't instantaneous. It takes a few seconds for other users to "detect" that the record is locked, so if two people try to open the same record at roughly the same time, it will allow them both to open it. I've applied a transaction to the UPDATE statement that sets the lock, but it still leaves a short window wherein the lock doesn't "take" and two people can open the same record.
那么，有没有一种方法可以使UPDATE瞬时(以便所有其他用户立即看到它的结果)，或者更好的是，一种健壮而全面的方法来在Access多用户环境中锁定记录?
So, is there a way to make an UPDATE instantaneous (so all other users immediately see its results), or better yet, a robust and comprehensive way to lock records in an Access multi-user environment?
推荐答案
好，我终于找出了所有导致此问题的原因并提出了解决方案.
OK, I finally figured out all of the issues contributing to this and worked out a solution.
问题是多方面的，所以我将分别讨论这些问题:
The problem is multi-faceted so I'll cover the issues separately:
第一个问题:我的自定义锁不是瞬时的.即使我正在使用事务，在放置锁之后的几秒钟内，用户仍然可以同时访问同一记录.我正在使用CurrentDb.Execute来更新记录和该事务的Workspaces(0).BeginTrans.出于某种原因(尽管Microsoft从此处做出了相反的保证: https://msdn.microsoft.com/zh-CN/library/office/ff197654.aspx )，问题是使用Workspaces对象时事务无法正常工作.当我切换到DBEngine.BeginTrans时，锁是瞬时的，解决了我眼前的问题.
First issue: My custom locks weren't instantaneous. Even though I was using a transaction, there were several seconds after a lock was placed where users could still access the same record at the same time. I was using CurrentDb.Execute to UPDATE the record and Workspaces(0).BeginTrans for the transaction. For some reason (despite Microsoft's assurances to the contrary from here: https://msdn.microsoft.com/en-us/library/office/ff197654.aspx) the issue was that the transaction wasn't working when using the Workspaces object. When I switched to DBEngine.BeginTrans, the lock was instantaneous and solved my immediate problem.
具有讽刺意味的是，我几乎总是使用DBEngine进行事务，但是这次却无缘无故地使用了Workspaces，因此显然是个不好的举动.
The irony is that I almost always use DBEngine for my transactions but went with Workspaces this time for no reason, so that was a bad move obviously.
第二个问题:我必须首先使用自定义锁定的原因是因为记录级锁定未按预期工作(尽管配置正确).它仍在使用页面级锁定.这是由于我从此处使用的性能技巧: https://msdn.microsoft.com/zh-cn/library/dd942824%28v=office.12%29.aspx?f=255&MSPPError=-2147217396
Second issue: The reason I had to use custom locking in the first place was because record-level locking wasn't working as expected (despite being properly configured). It was still using page-level locking. This was due to a performance trick I was using from here: https://msdn.microsoft.com/en-us/library/dd942824%28v=office.12%29.aspx?f=255&MSPPError=-2147217396
技巧包括打开到包含链接表的数据库的连接，这可以加快链接表的操作.问题在于，OpenDatabase方法与记录级锁定不兼容，因此它使用页面级锁定打开数据库，并且由于第一个打开数据库的用户确定了其锁定级别(如下所述: https://msdn.microsoft.com/zh-CN/library/aa189633 (v = office.10).aspx )，则所有后续连接均被强制为页面级.
The trick involves opening a connection to the database where your linked tables are contained, which speeds up linked table operations. The problem is that the OpenDatabase method is NOT compatible with record-level locking so it opens the db using page-level locking, and since the first user to open a database determines its lock level (as explained here: https://msdn.microsoft.com/en-us/library/aa189633(v=office.10).aspx), all subsequent connections were forced to page-level.
第三个问题::我的问题是我的表单不仅是将表单简单绑定到单个表的表单.他们打开一条记录(不允许用户浏览)并提供多种功能，这些功能使用户可以进行修改，这些修改会影响其他表中与他们正在编辑的记录相关的其他记录(通过组合框和弹出式表格，以及不是).结果，我不能允许两个人同时打开同一条记录，因为这给用户留下了太多的机会来浏览彼此的更改.因此，即使我删除了OpenDatabase的性能技巧，我仍然必须在他们打开窗体后立即强制将其设置为Dirty，以便记录立即锁定并且没有其他人可以打开它.我不知道这是否与我的自定义锁定一样瞬间，并且尚未测试该方面.
Third issue: My problem is that my forms are not just simple bound forms to a single table. They open a single record (not allowing the user to navigate) and provide several functions which allow the user make modifications which affect other records in other tables that are related to the record they're editing (through comboboxes and pop-up forms and what not). As a result, I can't allow two people to open the same record at the same time as it leaves way too many opportunities for users to walk over each others' changes. So even if I remove the OpenDatabase performance trick, I'd still have to force the Form to be Dirty as soon as they open it so the record locks immediately and no one else can open it. I don't know if this would be as instantaneous as my custom locking and haven't yet tested that aspect.
无论如何，我需要在用户打开记录后立即对其进行锁定，而现在我决定继续使用我的自定义锁定(带有事务修复程序).如果发现有其他事情使它不理想，我可以尝试删除OpenDatabase技巧，并切换到Access的内置锁定，并在打开每个记录时立即对它进行立即锁定.
In any event, I need a record to be locked the instant a user opens it and for now I've decided to keep using my custom locking (with the fix for the transaction). If something else comes to light that makes that less than ideal, I can try removing the OpenDatabase trick and switching to Access's built-in locking and force an immediate lock on every record when it is opened.
这篇关于即刻“锁定"多用户访问环境中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！