我应该如何锁定这个 VB6 / Access 应用程序中的表？
我正在开发一个使用 Access 数据库的 VB6 应用程序。应用程序不时将消息写入日志表。应用程序的多个实例可能同时运行，为了区分它们，每个实例都有自己的运行号。因此，运行数是从日志表中推导出来的...
Set record_set = New ADODB.Recordset
query_string = "SELECT MAX(RUN_NUMBER) + 1 AS NEW_RUN_NUMBER FROM ERROR_LOG"
record_set.CursorLocation = adUseClient
record_set.Open query_string, database_connection, adOpenStatic, , adCmdText
record_set.MoveLast
If IsNull(record_set.Fields("NEW_RUN_NUMBER")) Then
run_number = 0
Else
run_number = record_set.Fields("NEW_RUN_NUMBER")
End If
command_string = "INSERT INTO ERROR_LOG (RUN_NUMBER, SEVERITY, MESSAGE) " & _
" VALUES (" & Str$(run_number) & ", " & _
" " & Str$(SEVERITY_INFORMATION) & ", " & _
" 'Run Started'); "
database_connection.Execute command_string
显然，运行数的计算与数据库中新行的出现之间存在很小的差距，为了防止另一个实例在两个操作之间进行访问，我' d 想锁定桌子；类似于
SET TRANSACTION READ WRITE RESERVING ERROR_LOG FOR PROTECTED WRITE;
我应该如何去做这件事？锁定记录集有什么好处吗（记录集中的行与数据库中的任何特定行都不匹配）？
I'm working on a VB6 application using an Access database. The application writes messages to a log table from time to time. Several instances of the application may be running simultaneously and to distinguish them they each have their own run number. The run number is deduced from the log table thus...
Set record_set = New ADODB.Recordset
query_string = "SELECT MAX(RUN_NUMBER) + 1 AS NEW_RUN_NUMBER FROM ERROR_LOG"
record_set.CursorLocation = adUseClient
record_set.Open query_string, database_connection, adOpenStatic, , adCmdText
record_set.MoveLast
If IsNull(record_set.Fields("NEW_RUN_NUMBER")) Then
run_number = 0
Else
run_number = record_set.Fields("NEW_RUN_NUMBER")
End If
command_string = "INSERT INTO ERROR_LOG (RUN_NUMBER, SEVERITY, MESSAGE) " & _
" VALUES (" & Str$(run_number) & ", " & _
" " & Str$(SEVERITY_INFORMATION) & ", " & _
" 'Run Started'); "
database_connection.Execute command_string
Obviously there is a small gap between the calculation of the run number and the appearance of the new row in the database, and to prevent another instance getting access between the two operations I'd like to lock the table; something along the lines of
SET TRANSACTION READ WRITE RESERVING ERROR_LOG FOR PROTECTED WRITE;
How should I go about doing this? Would locking the recordset do any good (the row in the record set doesn't match any particular row in the database)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
关于添加表的评论是准确的，但为了直接回答您的问题（至少是......），记录锁定策略由 Connection 对象上的选项控制。检查 ADO 帮助。
The comments about adding a table are spot-on, but to directly answer your question (sort of, at least...), the record locking strategy is controled by options on the Connection object. Check the ADO help.
如何在 Jet 4.0 和 ADO 中实现多用户自定义计数器
How To Implement Multiuser Custom Counters in Jet 4.0 and ADO
为了将我的所有评论总结为一个答案（也感谢@MarkJ 的最初想法），
您需要创建一个名为 tblSession 或类似的表。该表将有一个自动编号主键，然后是一些辅助字段，例如用户名和计算机号等。当应用程序打开时，它会在此表中创建一条记录并读回 ID 号，然后应用程序将其用作其会话号。由于我们已将 ID 字段定义为唯一键，因此不会发出重复的数字。
To sum up all of my comments into an answer (also thanks to @MarkJ for the initial idea)
You need to make a table called tblSession or similar. This table would have an autonumber primary key and then a few helper fields such as user name and machine number etc. When the application opens it creates a record in this table and reads back the ID number, the application then uses this as its session number. As we have defined the ID field as a unique key it will not issues out duplicate numbers.