多用户数据库中访问自动编号和保持唯一性 [英] Access Autonumber and Maintaining Uniqueness in Multi-user Database

问题描述

关于 Access 的一些一般性问题，但希望有一个相当简单的答案:

Somewhat general question about Access, but hopefully has a fairly easy answer:

当您在共享的多用户数据库中使用自动编号时，Access 是否在做任何特别的事情来确保它分配的编号是唯一的，而其他用户同时添加记录?

When you use autonumber in a shared multi-user database, is Access doing anything in particular to ensure that the number it assigns is unique what with other users adding records at the same time?

它是立即为连接到该表的第一个用户申请下一个号码，还是等到新记录即将保存并在分配之前检查表中所有已保存记录的最大数量下一个号码?

Does it immediately claim the next number for the first user who has connected to that table, or does it wait until the new record is about to be saved and check the highest number of all saved records in the table just before assigning the next number?

它的独特性在多用户环境中是否稳健?

Is its uniqueness robust in a multi-user environment?

推荐答案

为了充分回答您的问题，区分

In order to fully answer your question it is important to make the distinction between

• 访问"(应用程序)和

• "Access" (the application), and

Access 数据库引擎"(又名ACE")及其前身 Jet 数据库引擎.

the "Access Database Engine" (a.k.a. "ACE") and its predecessor, the Jet database engine.

当您在共享的多用户数据库中使用自动编号时，Access 是否做了什么特别的事情来确保它分配的编号在其他用户同时添加记录时是唯一的?

When you use autonumber in a shared multi-user database, is Access doing anything in particular to ensure that the number it assigns is unique what with other users adding records at the same time?

[...]

它的独特性在多用户环境中是否稳健?

Is its uniqueness robust in a multi-user environment?

是的.ACE 与许多其他数据库引擎一样，通过通常在记录提交(写入)表时分配该编号来确保身份列(Access 称为自动编号字段")在多用户环境中是唯一的.但是，ACE 确实为 Access 提供了提前获取其 AutoNumber 值的机会(见下文).

Yes. ACE, like many other database engines, ensures that Identity columns (what Access calls "AutoNumber fields") are unique in a multi-user environment by normally assigning that number at the time the record is committed (written) to the table. However, ACE does offer Access the opportunity to get its AutoNumber value in advance (see below).

[Access] 是否立即为连接到该表的第一个用户申请下一个号码，

Does [Access] immediately claim the next number for the first user who has connected to that table,

没有.简单地连接到"一个表(例如，通过执行 SELECT，或打开一个 Recordset)不会影响自动编号字段的计数器.

No. Simply "connecting to" a table (e.g., by doing a SELECT, or opening a Recordset) does not affect the counter of an AutoNumber field.

或者[Access]是否等到新记录即将被保存并在分配下一个编号之前检查表中所有已保存记录的最高编号?

or does [Access] wait until the new record is about to be saved and check the highest number of all saved records in the table just before assigning the next number?

这取决于...

如果表"是指向 ODBC 数据源的链接表(例如，带有 IDENTITY 列的 SQL Server 表)，则访问等待"直到用户执行将提交(保存)新记录的操作，此时它将新记录提交给数据库服务器，然后检索该记录的自动编号值(例如，通过 SQL Server 中的 SELECT @@IDENTITY，或其他数据库引擎的类似机制).

If the "table" is a linked table to an ODBC data source (e.g., a SQL Server table with an IDENTITY column) then yes, Access "waits" until the user does something that will commit (save) the new record, at which point it submits the new record to the database server and then retrieves the AutoNumber value for that record (e.g., via SELECT @@IDENTITY in SQL Server, or a similar mechanism for other database engines).

但是，如果表"是本机 ACE/Jet 表，那么您可能会注意到，在您开始输入新记录(例如，在数据表视图或绑定表单中)后，新的自动编号值会立即出现.在这种情况下，Access(应用程序)告诉 ACE(数据库引擎)它可能想要插入新记录并立即请求 AutoNumber 值.ACE 返回该值并递增计数器，因此发出相同请求的另一个用户将获得序列中的下一个数字.请注意，此过程消耗"了 AutoNumber 值:它将被使用(如果用户保存记录)或丢弃(如果用户决定不保存记录)，但不会重新使用.这就是为什么

However, if the "table" is a native ACE/Jet table then you may notice that the new AutoNumber value appears immediately after you start typing into a new record (e.g., in Datasheet View, or in a bound form). In this case Access (the application) tells ACE (the database engine) that it may want to insert a new record and requests the AutoNumber value right away. ACE returns the value and increments the counter so another user making the same request will get the next number in the sequence. Note that this process "consumes" the AutoNumber value: it will either be used (if the user saves the record) or discarded (if the user decides not to save the record) but it won't be re-used. That's why

• 递增"(相对于随机")的 ACE 自动编号字段有时会存在间隙"，并且

• ACE AutoNumber fields that are "incrementing" (as opposed to "random") sometimes have "gaps" in them, and

如果您开始输入数据，点击 Esc 取消插入，然后再次开始输入，自动编号值是不同的(因为您消耗"了之前的自动编号值，即使您没有保存记录).

if you start entering data, hit Esc to cancel the insert, and then start typing again, the AutoNumber value is different (because you "consumed" the previous AutoNumber value even though you did not save the record).

这篇关于多用户数据库中访问自动编号和保持唯一性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！