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

1- Create table1 2- Go to create ------- > design query. 3- Close table 4- Go to SQl from above. 5- Past this code.

ALTER TABLE [TABLE1] ALTER COLUMN [Id] COUNTER (8982,1)

Answer 3

This website has a very simple way to start your autonumber with whatever number you wish: http://www.fmsinc.com/microsoftaccess/AutoNumber%20Field/Creating.asp

You create a query with an expression which tells the autonumber field to change

"First, run this query:

INSERT INTO tblName ( ID ) SELECT 999 AS Expr1

Where tblName is the table name, and ID is the name of the AutoNumber field.

Then, run a second query to delete the row that you inserted:

DELETE FROM tblName WHERE ID=999;

(Alternatively, you can simply open the table and manually delete the row that you inserted.)

Now, the next record added to the table is assigned the value 1000.

This method works with a new table, or with a table whose AutoNumber has not yet reached the Seed value. (Of course, the ID field for existing rows will not be changed, but subsequent rows will start with 1000.)"

Answer 4

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.

Answer 5

This is a pretty old thread, but still pops up as a top three Google search result.

One reason a user may want to change the value of an autonumber field is to correct an error caused by database editing. When an administrator may want to make some major changes to a front end of a database, that admin might make copies of all related tables, and redirect a development copy of the front end to these back ends. If the changes are sufficient enough, he may just replace the production copy of the front end with his copy, redirecting all linked tables after testing.

No problem, right? Well, the autonumber incrementation stays with the front end. So if the production side just incremented the autonumber by 50 or 500 or 5000 numbers while the admin was working on the copy, then the new production copy will by retrying those same numbers and won't be able to enter items into those fields, one at a time, until it gets "in the clear". Copy 50, 500, or 5000 rows of excel data and try to paste it in. It will error on each line (be sure to suppress further messages when it pops up), but the autonumber will increment by that amount and you're good to go again. If you're using autonumbers in this way, it would be advised to increment the numbers to whatever the production db is at before overwriting it with your development copy.

This is also a way to start your autonumber at something higher than 1. Like the old days of setting the page visits counter at 17,479 on your Geocities homepage, when really it was only visited 16 times. By you.

Answer 6

A simple way to achieve something similar is to have an Autonumber field (eg "ID") and a second Calculated field (eg "ItemID") and enter the formula "1000+[ID]" for the calculated field. This new field will now automatically track the Autonumber field, but will start from 1001 instead of 1.

Answer 7

I believe it could be achieve to your custom increment range by first creating a hidden table which will keep the initial gap of auto increments begining from zero to your choice , eg. that table will keep the 0 to 1050 Then you commence at of increments 1055 ,1060, 1065 etc. You will then hide this portion of data from the table. Hope this helps. Seems like Access was designed to look initially from zero, as conflicts will arise.