Changing the Seed and Increment Value of Auto Number Columns

When a table contains a field with an AutoNumber data type, you can set a seed value and an increment value. The seed value is the initial value for the column, and the increment value is the number added to the seed value to obtain a new counter value for the next record. If not specified, both seed and increment values default to 1. You can use the DDL to change the seed and increment values of AutoNumber columns by using one of the following three statements:

The example procedure in Hands-On 19-15 modifies the seed value of the existing AutoNumber column in the SchoolId column to start at 1000. Because we changed the SchoolId column's data type to the Text data type in one of the

Programming with the Jet Data Definition Language earlier hands-on exercises, you will modify the SchoolId column in the Sites.mdb file you created in Hands-On 19-3 at the beginning of this chapter.

This hands-on uses the Sites.mdb database file and tblSchools table created in Hands-On 19-3.

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the ChangeAutoNumber procedure as shown below.

3. Run the ChangeAutoNumber procedure.

4. Launch Microsoft Office Access with the Sites.mdb database and open the tblSchools table.

5. Enter a couple of new records in this table. In the YearEstablished field enter the date in the format mm/dd/yyyy. Note that the first new record is numbered 1000, the second 1001, the third 1002, and so on.