如何重置访问表的"自动编号"字段？(它不是从 1 开始的)

Question

>我有一个INSERT INTO ... SELECT语句，将数据从一个表复制到另一个表。

问题是，第二个表中的自动编号列值从第一个表中的最后一个数字开始。
这意味着第一个表的计数是 2000，然后，第二个表从 2001 年开始。

使用 Access 数据库，如何重置此值？

Answer 1

可以从 ADO 执行访问 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)

如果表包含数据，则种子值必须大于最大存储值。 如果在执行语句时表为空，则不会成为问题。

Answer 2

看起来您唯一的选择是将数据移动到新表中。 以下链接包含有关如何根据您的访问版本执行此操作的一些信息。

注意：如果与其他表有关系，请小心，因为需要重新创建这些表。

http://support.microsoft.com/kb/812718

Answer 3

我遇到了有关如何设置Microsoft访问自动编号字段的值的小信息。

Setting the value of a Microsoft Access AutoNumber Field
Using an Append Query to Set the Initial Value of a Microsoft Access AutoNumber Field:
By using an append query, you can change the starting value of an AutoNumber field in a table
to a number other than 1.
Microsoft Access always numbers AutoNumber fields beginning with the number 1. 
If the table has  data in it already, the starting value of the autonumber will be higher 
than the highest value already in the table. You cannot manually edit an AutoNumber 
field or change its starting value.
Overview: Changing Initial Value of an AutoNumber Field
In order to force Microsoft Access to number an AutoNumber field with a number you choose,   
follow these general steps below:
For a new table that contains no records, you can change the starting value of an AutoNumber
field that has its NewValues property set to Increment to a number other than 1. For a table 
that contains records, you can also use this procedure to change the next value assigned in an
AutoNumber field to a new number.
    1. Create a temporary table with just one field, a Number field; set its FieldSize 
       property to Long Integer and give it the same name as the AutoNumber field in the table
       whose value you want to change.
    2. In Datasheet view, enter a value in the Number field of the temporary table that is 1 
       less than the starting value you want for the AutoNumber field. For example, if you want
       the AutoNumber field to start at 100, enter 99 in the Number field.
    3. Create and run an append query to append the temporary table to the table whose 
       AutoNumber value you want to change. 
Note: If your original table has a primary key, you must temporarily remove the primary key 
before running the append query. Also, if your original table contains fields that have the
Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or 
record ValidationRule property settings that prevent Null entries in fields, you must 
temporarily disable these settings.
     4. Delete the temporary table.
     5. Delete the record added by the append query. 
     6. If you had to disable property settings in step 3, return them to their original 
        settings.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field 
value 1 greater than the value you entered in the temporary table. 
Note: If you want to compact the database after changing the starting AutoNumber value, make
sure to add at least one record to the table first. If you don't, when you compact the database,
the AutoNumber value for the next record added will be reset to 1 more than the highest previous
value. For example, if there were no records in the table when you reset the starting value,
compacting would set the AutoNumber value for the next record added to 1; if there were records
in the table when you reset the starting value and the highest previous value was 50, compacting 
would set the AutoNumber value for the next record added to 51.

它对我有用。只需按照信件中的说明进行操作即可。不像我跳过它。我发现了完全按照它所说的去做的困难方法。如果我正确阅读您的问题，我希望它会对您有所帮助。 我重新启动自动编号字段以4556363其中包含一个包含 8500 条记录的表，它没有更改任何内容，只是自动编号字段。我希望这不会迟到。史蒂文