vba vba中的记录锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1212505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Record locking in vba
提问by Fionnuala
i have a VBA application which is spitted into two one for front end; and backend while updating how to prevent two users edit it ??
我有一个 VBA 应用程序，它被分成两个前端用于前端；和后端同时更新如何防止两个用户编辑它？
回答by Fionnuala
Access has locking. From Access Help:
访问有锁定。从访问帮助：
Specify the locking level used in an Access database in a multiuser environment
指定在多用户环境中的 Access 数据库中使用的锁定级别
On the Tools menu, click Options.
Click the Advanced tab.
To make record-level locking the new default setting for the current Microsoft Access database, select the Open databases using record-level locking check box.
To make page-level locking the new default setting for the current Access database, clear the Open databases using record level locking check box.Notes
This setting takes place the next time you open the Access database, but you must use the Open command on the File menu rather than the list of most recently used files at the end of the File menu. This behavior is the same as the setting for the default open mode.
If you select Open databases using record level locking, this becomes the default behavior for access to data through a form, a datasheet, and code that uses a recordset object to loop through records, but not through action queries, nor through code that performs bulk operations using SQL statements. For more information, see Chapter 16, "Multiuser Database Applications," in the Microsoft Office 2000/Visual Basic Programmer's Guide.
在工具菜单上，单击选项。
单击高级选项卡。
要将记录级锁定作为当前 Microsoft Access 数据库的新默认设置，请选中“使用记录级锁定打开数据库”复选框。
要将页级锁定设为当前 Access 数据库的新默认设置，请清除“使用记录级锁定打开数据库”复选框。笔记
下次打开 Access 数据库时会进行此设置，但您必须使用“文件”菜单上的“打开”命令，而不是“文件”菜单末尾的最近使用的文件列表。此行为与默认打开模式的设置相同。
如果您选择使用记录级锁定打开数据库，这将成为通过表单、数据表和使用记录集对象循环访问记录的代码访问数据的默认行为，但不是通过操作查询，也不是通过执行批量的代码使用 SQL 语句进行操作。有关详细信息，请参阅 Microsoft Office 2000/Visual Basic 程序员指南中的第 16 章“多用户数据库应用程序”。
回答by Bogdan_Ch
Yoy may try to invoke pessimistic locking
Yoy 可能会尝试调用悲观锁定
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.ConnectionString = " Provider=sqloledb;" & _
"Data Source=(local);Initial Catalog=pubs;uid=sa;pwd="
cnn.Open
Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking
rst.CursorLocation = adUseServer
rst.Open "Select * from Table Where ID ='" _
& strID & "'", _
Options:=adCmdText
rst!Name = "New name" 'Lock occurs here
'... when it is locked, you may do other operations
rst.Update 'Lock Released Here
You will have to implement error handling, because when 2nd client wants to edit and cannot lock the record during timeout, error will be raised.
您必须实现错误处理，因为当第二个客户端想要编辑并且在超时期间无法锁定记录时，将引发错误。
However pessimistic locking is not the best scenario, I would think about optimistic locking and either First Wins or Last Wins strategy
然而悲观锁定不是最好的方案，我会考虑乐观锁定和 First Wins 或 Last Wins 策略
Here is an online book Alison Balter's mastering Microsoft Access 2000 development, it should help you.
这是一本在线书籍Alison Balter's mastering Microsoft Access 2000 development，它应该对您有所帮助。
