How do I set the first value of AutoNumber in Access?

Question

I don't need to reset the AutoNumber value like this question, but rather would like to first set the number to:

1001

Which then increments like normal:

1001

1002

1003

...

How do I go about doing this in Access 2010?

Answer 1

Although Access doesn't offer this function natively, it can be achieved through a query, like the following:

CREATE TABLE TableThatIncrements
(
Id AUTOINCREMENT(1001,1)
)

This will create a single table that's called "TableThatIncrements" with a single column, named "Id". You can now use the table editor to add properties and/or other columns.

Change

AUTOINCREMENT(x,y)

to suit your needs, where x is the initial increment number and y is the number to increment with. So AUTOINCREMENT(100,5) will yield: 100, 105, 110, 115, etc.

If you want to alter an existing table, use the following query. Make sure that specific table's tab is closed so Access can lock it down and alter it.

ALTER TABLE TableThatIncrements
   ALTER COLUMN Id AUTOINCREMENT(1001,1)

---

You can run a query in Access by doing the following:

1. Go to the "Create" tab and click "Query Design"
   

2. Just close the window that appears which asks you to select tables, we don't need that.

3. Go to the "Design" tab and click the button with the arrow until you get a textual input screen. (By default, it says SELECT;).
   

4. Delete the default text and paste the above query.

5. Click "Run".
   

Answer 2

You have to trick Access because it assumes you're not interested in an autonumber different than the one it provides.

You'll need to start the table out with the custom autonumber, which will require some setup. But once it's going you can just continue adding from there.

Google "access change autonumber start" and you'll find several options for doing this. Most rely on creating an append query to modify the field.

Create the first table that contains the counter type field that you want to start on another number. Don't enter any records.Create a second table, with a single long integer number type field that has the same name as the counter field in the first table. Create a record in the second table by entering a number one less than the number you want to start the counter at in the first table. Create an append query, to append the one record in the second table to the first table, and run it Delete the second table, delete the record that you appended to the first table, and start entering data.

From MVPS.org: http://access.mvps.org/access/tables/tbl0005.htm

If you don't feel like building an append query, you could always set up the table and copy and paste 1000 lines of dummy data from excel, delete the records until you get to number 1001, and go on from there.

UPDATE: Adding explanation for reason

The reason behind autonumber being only set to start from 1 is that the autonumber field is meant to be used as an internal reference field, and in good database practice, should not generally be used in any publicly visible form. It is used in cases where there is not already a unique item in a given table to create a unique entry by which the table can be searched and indexed.