如何重置自动编号序列？ [英] How to reset AutoNumber sequence?

问题描述

有没有办法重置自动编号序列？


我有几个表使用自动编号字段作为主键，并且

我'我想以某种方式做一个导入/导出，将删除

中断的序列。按顺序进行一些休息并不是什么大不了的事，但是我有一张不超过200条记录的
表，但最后一个AutoNumber PK ID字段结束了

1500 - 由于很多编辑....

Is there a way to reset the AutoNumber sequence?

I have several tables that use the AutoNumber field as the Primary Key, and
I''d like to somehow do an Import/Export that will make remove the breaks in
the sequence. A few breaks in sequence is not a big deal, but I have one
table with under 200 records, but the last AutoNumber PK ID field is over
1500 - due to a lot of edits....

推荐答案

deko，


*创建新表并导入所有除了自动编号之外的字段 -

如果您有父/子关系会有问题。


*删除自动编号字段，保存表格，修复并压缩数据库

（仅仅因为），并添加一个自动编号字段 - 再次，如果你有
有父/子关系会有问题。


建议（不是讽刺）：不要挂断顺序

自动编号，小型自动编号或自动编号与表中

记录的数量相匹配。我有8位自动编号的SQL Server表

（SQL中的IDENTITY）。


您的pk字段不能用于帐号，发票号码等等，

对吗？所以它只是一个独特的数字，而且很小 - 让它成为。


" deko" < DJ **** @ hotmail.com>在消息中写道

新闻：BC *********** @ newssvr29.news.prodigy.com ...

deko,

* create new tables and import all fields except the AutoNumber -
problematic if you have parent/child relationships.

* drop the AutoNumber field, save the table, repair and compact the database
(just because), and add back an AutoNumber field - again, problematic if you
have parent/child relationships.

Recommendation (not being sarcastic): don''t get hung up on sequential
AutoNumbers, or small AutoNumbers, or AutoNumbers matching the number of
records in the table. I have SQL Server tables with 8-digit AutoNumbers
(IDENTITYs in SQL).

Your pk fields aren''t used for Account Numbers, Invoice Numbers, etc.,
right? So it''s just a unique number, and small - let it be.

"deko" <dj****@hotmail.com> wrote in message
news:BC***********@newssvr29.news.prodigy.com...

有没有办法重置自动编号序列？

我有几个表使用自动编号字段作为主键，
我想以某种方式做一个导入/导出，将删除中断
顺序。序列中的一些中断并不是什么大不了的事，但是我有一个不超过200条记录的表，但最后一个AutoNumber PK ID字段已经超过了1500 - 由于大量的编辑....

Is there a way to reset the AutoNumber sequence?

I have several tables that use the AutoNumber field as the Primary Key, and I''d like to somehow do an Import/Export that will make remove the breaks in the sequence. A few breaks in sequence is not a big deal, but I have one
table with under 200 records, but the last AutoNumber PK ID field is over
1500 - due to a lot of edits....

>您的pk字段不能用于帐号，发票号等，

> Your pk fields aren''t used for Account Numbers, Invoice Numbers, etc.,

对吗？所以它只是一个唯一的数字，而且很小 - 让它成为。

right? So it''s just a unique number, and small - let it be.

ID号对用户可见，可用于搜索，但

序列并不重要。我不希望Entity表超过
100,000条记录，4或5位Entity_ID就好了。但是我希望

帮助条目为Entity_ID 1 - 清除测试数据

并插入帮助条目作为第一条记录会很好。


也许我可以创建一个空白数据库，只导入表结构，

然后删除原始数据库上的ID字段，并只导入数据 -

这将重新排序所有AutoNumber字段？如何在没有结构的情况下导入表格

数据？


我可以使用此代码而不是自动编号吗？或者这是不必要的开销吗？


私有子Form_BeforeInsert（取消为整数）


Dim db作为DAO.Database

Dim rst作为DAO.Recordset

设置db = CurrentDb

设置rst = db.OpenRecordset（"选择max（Entity_ID）as imax from

tblEntity"）

rst.MoveFirst

Me！Entity_ID = rst！imax + 1

rst.Close

设置db = Nothing

设置rst =无

End Sub

The ID numbers are visible to the user and can be used for searching, but
sequence is not really important. I don''t expect the Entity table to exceed
100,000 records, and a 4 or 5 digit Entity_ID is just fine. But I''d like
the "Help" entry to be Entity_ID 1 - it would be nice to purge the test data
and insert the Help entry as the first record.

Perhaps I could create a blank database, import just the table structures,
then drop the ID fields on the original database, and import just the data -
would this re-order all the AutoNumber fields? How can I import the table
data without the structures?

Could I use this code instead of Auto Number? Or is this needless overhead?

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Select max(Entity_ID) as imax from
tblEntity")
rst.MoveFirst
Me!Entity_ID = rst!imax + 1
rst.Close
Set db = Nothing
Set rst = nothing

End Sub

On Tue ，2004年1月13日06:17:24 GMT in comp.databases.ms-access，" deko"

< dj **** @ hotmail.com>写道：

On Tue, 13 Jan 2004 06:17:24 GMT in comp.databases.ms-access, "deko"
<dj****@hotmail.com> wrote:

您的PK字段不能用于帐号，发票号码等，对吧？所以它只是一个唯一的数字，而且很小 - 让它成为。
ID号码对用户可见，可用于搜索，但序列并不重要。我不希望Entity表超过100,000条记录，4或5位Entity_ID就好了。但是我想要帮助。条目为Entity_ID 1 - 最好清除测试数据
并插入帮助条目作为第一条记录。

Your pk fields aren''t used for Account Numbers, Invoice Numbers, etc.,
right? So it''s just a unique number, and small - let it be.
The ID numbers are visible to the user and can be used for searching, but
sequence is not really important. I don''t expect the Entity table to exceed
100,000 records, and a 4 or 5 digit Entity_ID is just fine. But I''d like
the "Help" entry to be Entity_ID 1 - it would be nice to purge the test data
and insert the Help entry as the first record.

压缩通常会重置为表中最高的数字+ 1因此

在删除测试数据后紧凑。

也许我可以创建一个空白数据库，只导入表格结构，然后删除原始数据库上的ID字段，只导入数据 -
这会重新排序所有自动编号字段吗？如何在没有结构的情况下导入表格数据？


使用导入向导，导入到现有表格中，或者您可以从其他数据库链接表格并运行一系列追加

查询。

我可以使用此代码而不是自动编号吗？或者这是不必要的开销吗？

Private Sub Form_BeforeInsert（取消为整数）

Dim db As DAO.Database
Dim rst as DAO.Recordset
设置db = CurrentDb
设置rst = db.OpenRecordset（"选择max（Entity_ID）as imax from
tblEntity"）
rst.MoveFirst
我！Entity_ID = rst！imax + 1
rst.Close
设置db = Nothing
设置rst =无

End Sub

Compacting usually resets to the highest number in the table + 1 so
compact after deleting test data.
Perhaps I could create a blank database, import just the table structures,
then drop the ID fields on the original database, and import just the data -
would this re-order all the AutoNumber fields? How can I import the table
data without the structures?
Using the import wizard, import into an existing table, or you can
link the tables from the other database and run a series of append
queries.
Could I use this code instead of Auto Number? Or is this needless overhead?

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Select max(Entity_ID) as imax from
tblEntity")
rst.MoveFirst
Me!Entity_ID = rst!imax + 1
rst.Close
Set db = Nothing
Set rst = nothing

End Sub

这是一个开销，无论是不必要的是意见问题，

职业选手可以超过缺点。这样你就不会有任何差距（直到某人

删除记录）。滚动你自己的自动编号的方法有很多种，从简单的DMax（）+ 1到更复杂的表格和/或
表名和自动编号的
值，后者检查目标

表以防它已经存在并添加另外1等等，这将意味着你可以重置为1并重新使用任何差距留下来删除。


但是我不会早在Form_BeforeInsert（）中这样做，因为你会支持

尽快复制这个数字人们在同一时间输入一个记录

时间。也许在Form_BeforeUpdate（）中检查是否在新的
记录中或者ID是否为空。


-

A）bort，R）etry，I）用大锤子撞击。

It''s an overhead, whether it''s needless is a matter of opinion, the
pros can outweigh the cons. This way you''ll get no gaps (until someone
deletes a record). There''s many ways of rolling your own autonumber,
from the simple DMax()+1 to the more complex of having a table full of
table names and autonumber values, the latter checking the target
table in case it already exists and adding another 1, etc, which would
mean you could reset to 1 and re-use any gaps left by deletion.

However I wouldn''t do this as early as Form_BeforeInsert() as you''ll
duplicate the number as soon as two people enter a record at the same
time. Perhaps in Form_BeforeUpdate() and check to see if on a new
record or that the ID is null first.

--
A)bort, R)etry, I)nfluence with large hammer.

这篇关于如何重置自动编号序列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！