The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
December 2011

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 goal.

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.

In this Issue
  • Tip of the Month
  • Beginning Auto Number at a Desired Number
  • Zen: The Art of Database Management
  • Trap of the Month

  • Beginning Auto 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 Database Management

    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 linking.

    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 Month

    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 Month

    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 table.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886