• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

Set Beginning AutoNumber ID Other than 1

I am creating a new table with the first field being 'ID' with a type of 'autonumber'.  Their are many other fileds in the table, all containing data.

How can I get the ID of the first record added to be something other than 1, such as 1000.  Second ID would be 1001, then 1002 etc..

I know I can do this by creating the new table, adding 999 records and then delteting them all.  I was hoping there was a simpler way since the real number I want as the first ID is 11,000,000.
1 Solution
Dave BaldwinFixer of ProblemsCommented:
I hope you're not going to use that as a 'serial number'.  In any case, you should be able to insert an initial record with that number and every record after that should add 1 to that number.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Here you go:

** VBA Code method - Example**
Place this code in a standard module. Do not name the module the same as the Function below.

Public Function mResetAutoNumber(lStartVal As Long, lIncrement As Long) As String
   'Example: Set the Starting Value and Increment for a table named Table1ANTest and an AutoNumber field called AutoNum
    Dim sSQL As String
    sSQL = "ALTER TABLE [Table1ANTest] ALTER COLUMN [AutoNum] COUNTER (" & lStartVal & ", " & lIncrement & ");"
    CurrentDb.Execute sSQL
    mResetAutoNumber = "Auto Number has been re-numbered"
End Function

The first number (lStartVal) is the starting value, and the second (lIncrement) is the increment.
The example above will start the Auto Number at 1000 and increment by 50.

To run the code one time, open the VBA Immediate Window, and type
this line (including the question mark, followed by Enter:

?mResetAutoNumber(<YourStartValueNumber>, <YourIncrementNumber>)

You can run this against an empty existing table, or to modify the *next higher* Auto Number value and increment.
It will *not* change any existing auto number values.

This works for A2K and later.
You can create a query like this which would insert ID number 10,999,999.  Then just delete that record and the next record you add would start at 11,000,000.

INSERT INTO NameOfTable ( ID ) SELECT 10999999;

Open in new window

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Luke ChungPresidentCommented:
Here's our paper that shows how to do this: http://www.fmsinc.com/microsoftaccess/autonumber-field/index.htm

Part of our Microsoft Access developer center: http://www.fmsinc.com/microsoftaccess/developer/index.html
mlcktmguyAuthor Commented:
Very easy, worked exactly as desired.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Just curious ... what version of Access are you using ?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now