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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Architect / Systems AnalystCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Luke ChungPresidentCommented:
Here's our paper that shows how to do this:

Part of our Microsoft Access developer center:
mlcktmguyAuthor Commented:
Very easy, worked exactly as desired.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Just curious ... what version of Access are you using ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.