更新后的叶子在主键空白 [英] afterupdate leaves gaps in primary key

问题描述

我开发了一些形式的仅用于数据输入的Access数据库。问题是，当我打开任何这些形式，进入创建基础表中一个新条目，其中包括递增基础表的自动编号主键，即使用户选择不建立在记录中的自动编号仍然增加数据库。这意味着有由于所有的时候用户间隙autonumbers在实际的表的顺序开放，没有提交修改关闭窗体。

I am developing an access database with some forms that are only used for data entry. The problem is that, when I open any of these forms, access creates a new entry in the underlying table, including incrementing the autonumber primary key of the underlying table, and the autonumber remains incremented even if the user opts not to create the record in the database. This means that there are gaps in the sequence of autonumbers in the actual table due to all the times users open and close the form without committing changes.

我使用下面的code在更新前的方法，让用户抛弃坏数据：

I am using the following code in the beforeupdate method to allow users to discard bad data:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Provide the user with the option to save/undo
    'changes made to the record in the form
    If MsgBox("Data has been entered into this form." _
        & vbCrLf & vbCrLf & "Do you want to save this data?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.Save
    Else
        DoCmd.RunCommand acCmdUndo
    End If
End Sub  

我原以为用beforeinsert事件，但有开放的形式，当beforeinsert使用的问题。

I had thought to use the beforeinsert event, but was having problems opening up the form when beforeinsert was used.

有人可以告诉我如何设置的东西，使这些差距没有得到自动编号值序列中产生的？该数据库将使用大约10个并发用户。

Can someone show me how to set things up so that these gaps do not get created in the sequence of autonumber values? This database will be used by perhaps 10 concurrent users.

推荐答案

您不应给予任何意义的自动编号字段的值。 Autonumbers允许您定义的每一行，并在这样做，他们都不能保证连续的独特价值。 我强烈建议你只使用自动编号字段来标识唯一的记录，而不是作为某种反或人类可读的价值。

You should not be assigning any significance to the autonumber field's value. Autonumbers allow you to define unique values for each row, and in doing so they are not guaranteed to be sequential. I strongly suggest you only use the autonumber field to identify unique records, not as some sort of counter or human-readable value.

现在，有了这样的方式，如果你需要每个记录真的有一个连续的值，那么你需要做的是

Now, with that out of the way, if you need each record to really have a sequential value, then what you need to do is

1. 创建一个单独的表，该表具有只有一行，一列保存的序列中的下一个值。
2. 每当一个记录从数据输入表单保存，你完全锁定的计数表，preventing任何其他用户读取或写入数据。
3. 从计数表的当前值和使用，在你的新dataentry记录。
4. 更新在计数表的值递增到下一个值。

Microsoft提供了一个功能，可以很容易地独占锁定一个表： DAOOpenTableExclusive（），它可以发现的这里。

Microsoft provides a function that makes it easy to exclusively lock a table: DAOOpenTableExclusive(), which can be found here.

您可以用code发现这里创建计数器表。

You can use the code found here to create a counter table.

这篇关于更新后的叶子在主键空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！