DAO记录集附加单记录锁整个表（链表） [英] DAO recordset append single record locks entire table (linked table)

问题描述

我在使用DAO记录集将记录附加到

表以及后续代码来更新事务中的其他表时遇到问题。

MDB是Access 2000，带有最新的JET 4服务包。系统是
客户端/服务器，基于多用户。 MDB使用记录锁定。


以下是代码的一部分：

Dim wkSpace As Workspace，db As Database

Dim rstTrans作为DAO.Recordset


设置wkSpace = DBEngine.Workspaces（0）

设置db = CurrentDb（）


wkSpace.BeginTrans


''一些代码......

设置rstTrans = db.OpenRecordset（" tblTrans"，dbOpenDynaset ，

dbAppendOnly，dbOptimistic）

使用rstTrans

.AddNew

''设置字段值...

。更新

。关闭

结束


''代码更新其他表格

wkSpace.CommitTrans


如果表是本地表，则该过程很好。但是如果表格

是链表，它会在执行Update方法后锁定整个tblTrans表。没有其他用户可以更新或附加tblTrans

表（无法更新;当前已锁定。错误）


我''我确定没有别的东西锁定表，因为我在前端和后端运行代码（只需

记录集附加代码）。


似乎锁只适用于本地表吗？

解决方案

我想锁定是由于交易造成的。


如果省略该行，看看是否有任何区别：

wkSpace.BeginTrans


-

Allen Browne - 微软MVP。西澳大利亚州珀斯。

访问用户提示 - http：// allenbrowne.com/tips.html

回复群组，而不是mvps dot org的allenbrowne。


" RayPower" < RA ******** @ yahoo.com.au>在消息中写道

news：11 ******************** @ u72g2000cwu.googlegrou ps.com ...

我在使用DAO记录集将记录追加到
表以及后续代码中更新事务中的其他表时遇到问题。 MDB是Access 2000，具有JET 4的最新服务包。该系统是基于客户端/服务器的多用户。 MDB正在使用记录锁定。

以下是代码的一部分：
Dim wkSpace As Workspace，db As Database
Dim rstTrans as DAO.Recordset
设置db = CurrentDb（）
wkSpace.BeginTrans

''一些代码......
设置rstTrans = db.OpenRecordset（" tblTrans"，dbOpenDynaset，
dbAppendOnly，dbOptimistic）
使用rstTrans
.AddNew
''设置字段值.. 。
。更新
。关闭
结束更新其他表格的代码

wkSpace.CommitTrans
表（使用无法更新;当前已锁定。错误）

我确定没有别的办法锁定表格因为我在前端和后端运行代码（只是
记录集附加代码）。

似乎锁只适用于本地表？

嗨艾伦，


感谢您的及时回复。


是的，我知道锁是由于交易。但我需要使用

事务，因为如果出现任何问题，所有更新都应该回滚。


问题在于表是链接表还是本地表。使用

本地表，tblTrans表将不会被锁定。但是使用链接

表，整个tblTrans表只是被锁定了，即使我只是
在该表中追加一条记录。


JET或Access 2000出了什么问题？

之前有没有人遇到过这个问题？

I''m having problem with using DAO recordset to append record into a
table and subsequent code to update other tables in a transaction. The
MDB is Access 2000 with the latest service pack of JET 4. The system is
client/server, multiusers based. The MDBs are using record locking.

Here is part of the code:
Dim wkSpace As Workspace, db As Database
Dim rstTrans As DAO.Recordset

Set wkSpace = DBEngine.Workspaces(0)
Set db = CurrentDb()

wkSpace.BeginTrans

''some code...

Set rstTrans = db.OpenRecordset("tblTrans", dbOpenDynaset,
dbAppendOnly, dbOptimistic)
With rstTrans
.AddNew
''set field values...
.Update
.Close
End With

''codes updating other tables

wkSpace.CommitTrans

The process is fine if the tables are local tables. But if the tables
are linked tables, it locks the entire tblTrans table once it execute
the Update method. No other user can update or append the tblTrans
table at all (with "Could not update; currently locked." error)

I''m sure that nothing else lock the table as I did run the code (just
the recordset append code) in the frontend and backend.

It seems the lock apply properly only to local tables?

解决方案

I imagine that the lock would be due to the transaction.

See if it makes any difference if you omit the line:
wkSpace.BeginTrans

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RayPower" <ra********@yahoo.com.au> wrote in message
news:11********************@u72g2000cwu.googlegrou ps.com...

I''m having problem with using DAO recordset to append record into a
table and subsequent code to update other tables in a transaction. The
MDB is Access 2000 with the latest service pack of JET 4. The system is
client/server, multiusers based. The MDBs are using record locking.

Here is part of the code:
Dim wkSpace As Workspace, db As Database
Dim rstTrans As DAO.Recordset

Set wkSpace = DBEngine.Workspaces(0)
Set db = CurrentDb()

wkSpace.BeginTrans

''some code...

Set rstTrans = db.OpenRecordset("tblTrans", dbOpenDynaset,
dbAppendOnly, dbOptimistic)
With rstTrans
.AddNew
''set field values...
.Update
.Close
End With

''codes updating other tables

wkSpace.CommitTrans

The process is fine if the tables are local tables. But if the tables
are linked tables, it locks the entire tblTrans table once it execute
the Update method. No other user can update or append the tblTrans
table at all (with "Could not update; currently locked." error)

I''m sure that nothing else lock the table as I did run the code (just
the recordset append code) in the frontend and backend.

It seems the lock apply properly only to local tables?

Hi Allen,

Thanks for your prompt reply.

Yes, I know the lock is due to the transaction. But I need to use
transaction as all updates should rollback if anything goes wrong.

The problem is whether the tables are link tables or local tables. With
local tables, the tblTrans table will not be locked. But with link
table, the entire tblTrans table just being locked even though I''m only
appending one record in that table.

Is that something wrong with JET or just Access 2000?

Did anyone encounter this problem before?

这篇关于DAO记录集附加单记录锁整个表（链表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！