Auto-Number field in Access

Greetings,

I have 1000+ records in access table and would like to add auto-number field and make it start from "10000010" and increment by 1.

Please suggest.
mrongAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
** 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>)
Example:
?mResetAutoNumber(500,3)

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.

**Data Definition SQL Query method - follow these steps:

1) Open a new query in design view.
2) Menu>>View>>SQL View
3) Enter the following SQL - all on one line:
ALTER TABLE [SomeTableName] ALTER COLUMN [YourAutoCounterFieldName] COUNTER(<YourStartValueNumber>,<YourIncrementNumber>);

<YourIncrementNumber> is optional.  Default is one.

Example:  ALTER TABLE [Table1] ALTER COLUMN [AutoNum] COUNTER(300,5);

4) Menu>>Query>>Run

That's it.

mx

********************
0
mrongAuthor Commented:
I have access 2013 and teid the following but didn't update.

**Data Definition SQL Query method - follow these steps:

1) Open a new query in design view.
2) Menu>>View>>SQL View
3) Enter the following SQL - all on one line:
ALTER TABLE [SomeTableName] ALTER COLUMN [YourAutoCounterFieldName] COUNTER(<YourStartValueNumber>,<YourIncrementNumber>);

<YourIncrementNumber> is optional.  Default is one.

Example:  ALTER TABLE [Table1] ALTER COLUMN [AutoNum] COUNTER(300,5);

4) Menu>>Query>>Run
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try the vba method
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mrongAuthor Commented:
Didn't work either.
0
PatHartmanCommented:
The no-code method is:

INSERT INTO YourTable (YourAutonumberField)
 VALUES (10000010);

Although you cannot modify the value of the autonumber field by typing into it, you can append records with existing values as long as there is no conflict with existing data.

If your table has required columns, you will need to modify the Insert to include them.

INSERT INTO YourTable (YourAutonumberField, fld5, fld12)
 VALUES (10000010, True, #1/1/2013#);

Once you add rows subsequent to this dummy one with higher ID values, you can go back and delete the dummy record.
0

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
FWIW ... both of the examples I posted do work, as I've used them many times.

mx
0
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.