Auto-Number Manipulation: Change the
Starting Point of Auto-Number Fields
Several weeks back, we here in the United States celebrated
Halloween. This is one of our most entertaining holidays. Children
dress up, changing themselves into monsters, princesses, and all
manner of different characters.
Sometimes, like at Halloween, it is pure fun to make a switch.
Other times (think witness protection), making a change is more of a
necessity. And sometimes you want to switch to meet some interim
One of the tools in Access tables is the availability of an
auto-number field, a field that clicks up one-by-one as new records
are added to the table. This usually acts as a primary key, a unique
identifier for each record. I've long preached that a primary key is
required and, in all cases, should be an auto number (June
2004). This month, we'll do a Halloween switch. Just as children
can dress up as something they're not, we can get Access do
something it doesn't usually do: Change the auto-number field to
start from whatever number makes you happy.
Number at a Desired Number|
Although I don't advocate changing the starting point for
an auto number (since it should be meaningless for us humans),
every once in a while I run across someone who has used the
auto number so that it has some significance for the users,
and the person in charge wants to make the auto number start
at a different spot.
Although this is not easy, it is possible.
Pull up the query design window and, when Access asks you
to select a table, click the close button so that no table
shows up in the top half of the window.
Change your query type to an append query. When you are
prompted for a table to append to, choose the table whose auto
number you would like to change.
In the first field of the query, enter the starting number
minus one that you would like for your primary key. For
example, enter the number 100 if you want it to start with
101. Then in the "Append To" row, choose your primary key
field, similar to what I show below. If your table has
required fields, you will have to append to those fields as
well. What you put in is not terribly import because you will
ultimately delete this new record.
Run the query. You will be asked if you really want to
append a row; say yes. Close the query, open your table, and
go to the last row, where you will see the record with the
primary key that you just created. Unless you have some
fondness for this particular record, go ahead and delete it.
When you enter a new record you will find that the auto
number will now be one more than what you used for your query.
|Zen: The Art of
Before you go through all the trouble of resetting your
auto number, consider just living with something other than a
particular starting point. The auto number is not there for us
to look at and manipulate or draw meaning from. Its purpose is
to uniquely identify a row. This is critical when tables are
If you really care about the number, it should be something
other than the primary key. Nothing prevents you from having a
number that you care about as well as an auto number that acts
as a primary key.
|Trap of the
Changing the auto number of a field, as shown in this
Wizard, is not something to do lightly. It has the
potential to cause huge problems. If you choose an auto number
lower than an existing number in your chosen field, as you add
new rows to the table the auto number will eventually click up
to that old number. At that point, your database will refuse
to add a new record. It refuses because it is trying to add a
primary key that already exists. Unless you know this is
coming and remember how to reset the auto number so that it
jumps ahead of existing numbers, you will have created a
problem that will take time and effort to fix.
Tip of the
If you have a table with no records, the process to reset
the primary key to the number one is easy. Simply compact and
repair, which will cause an auto number reset for any empty