如何在MS-Access中重置自动编号字段 [英] How To Reset The AutoNumber Field In MS-Access

问题描述

朋友

我在ms-access表中创建了一个表。

我创建了invoiceno字段为autonumber。

now我完成了项目。

i删除了表格中的所有测试数据。

我删除自动编号时是1027

完全删除数据后从表中。

i试图插入新记录。

自动编号即将到来1028我不想要1028我想从1开始（即从开始）。



所以我怎么能重置这个。





谢谢

解决方案

AFAIK你不能，不删除字段并重新创建它。



有充分的理由为此。

1）通常使用自动编号（不幸的是）作为表的索引。这意味着可能存在其他行，这些行通过前一个自动编号引用现在删除的行。如果您可以重置数字，那么这些行最终将与新行相关联。



在您的情况下，想象一下如果使用了它会导致什么麻烦自动编号作为发票编号，并在发票上有单独的项目表。然后，这些将通过自动编号返回发票。如果你重置了这个号码，那么一个顾客会突然收到他没有订购的30件物品的发票，而且还没有。麻烦随之而来。



就个人而言，我讨厌自动编号，只有在我永远不会引用它时才使用它：例如作为日志表的索引。如果您需要特定的发票编号序列（并且在大多数区域合法地执行），请考虑编写存储过程以分配新的发票编号，并使用Guid代替实际的数据库行参考。

< blockquote>删除表中的所有行后，压缩数据库应该将Autonumber值重置为1.



压缩数据库通过此链接 -



如何使用Visual Basic .NET压缩Microsoft Access数据库 [ ^ ]

您可以从ADO执行Access DDL语句以重置自动编号种子值。以下是立即窗口会话示例：
 
 strDdl =ALTER TABLE Dummy ALTER COLUMN ID COUNTER（1,1）; 
 CurrentProject.Connection.Execute strDdl 
 
该语句必须从ADO执行。如果您使用DAO（例如CurrentDb.Execute strDdl）或Access查询设计器尝试它，它将失败。该示例成功，因为CurrentProject.Connection是一个ADO对象。 
 
 COUNTER后面的两个值是种子和增量。因此，如果我希望自动编号从1000开始并递增2，我可以使用COUNTER（1000,2）
 
如果表包含数据，则种子值必须大于最大存储值。如果在执行语句时表格为空，那么这不是问题。

--------- ---

http://stackoverflow.com/questions/20738596/how-to-reset-an-access-tables-autonumber-field-it-didnt-start-from-1 [ ^ ]



信用转到 - > HansUp

Friends
I have created a table in ms-access table.
in that i created the invoiceno field as autonumber.
now i finished the project.
i deleted all the testing data in the table.
while im deleting the autonumber was 1027
after deleting the data completely from the table.
i tried to insert a new record.
the autonumber is coming 1028 i dont want 1028 i want to start from 1(ie. from beginning).

so how can i reset this.


thanks

解决方案

AFAIK you can't, without deleting the field and re-creating it.

There are good reasons for this.
1) Autonumber is often used (unfortunately) as an index to the table. This means there could exist other rows which refer to a now deleted row by the previous autonumber. If you could reset the number, then those rows would eventually be associated with a new row instead.

In your case, imagine what trouble it would cause if you used the autonumber as an invoice number, and had a separate tablw of items on the invoice. these would then refer back to the invoice by the autonumber. If you reset the number, suddenly a customer gets invoiced for thirty items he didn't order, and hasn't had. Trouble ensues.

Personally, I hate autonumber and only ever use it when I will never refer to it: as an index for a log table for example. If you need a specific sequence of invoice numbers (and legally you do in most areas) consider writing a stored procedure to allocate a new invoice number and use a Guid for the actual database row reference instead.

After you delete all of the rows in the table, compacting the database should reset the Autonumber value back to 1.

for Compacting database go through this link-

How to compact a Microsoft Access database by using Visual Basic .NET[^]

You can execute an Access DDL statement from ADO to reset the autonumber seed value. Here is an example Immediate window session:

strDdl = "ALTER TABLE Dummy ALTER COLUMN ID COUNTER(1, 1);"
CurrentProject.Connection.Execute strDdl

The statement must be executed from ADO. It will fail if you try it with DAO (such as CurrentDb.Execute strDdl), or from the Access query designer. The example succeeded because CurrentProject.Connection is an ADO object.

The two values following COUNTER are seed and increment. So if I wanted the autonumber to start from 1000 and increment by 2, I could use COUNTER(1000, 2)

If the table contains data, the seed value must be greater than the maximum stored value. If the table is empty when you execute the statement, that will not be an issue.

------------
http://stackoverflow.com/questions/20738596/how-to-reset-an-access-tables-autonumber-field-it-didnt-start-from-1[^]

Credit goes to -> HansUp

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