mrong
asked on
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.
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.
ASKER
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(<YourStartValueNum ber>,<Your IncrementN umber>);
<YourIncrementNumber> is optional. Default is one.
Example: ALTER TABLE [Table1] ALTER COLUMN [AutoNum] COUNTER(300,5);
4) Menu>>Query>>Run
**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]
<YourIncrementNumber> is optional. Default is one.
Example: ALTER TABLE [Table1] ALTER COLUMN [AutoNum] COUNTER(300,5);
4) Menu>>Query>>Run
Try the vba method
ASKER
Didn't work either.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FWIW ... both of the examples I posted do work, as I've used them many times.
mx
mx
Place this code in a standard module. Do not name the module the same as the Function below.
Public Function mResetAutoNumber(lStartVal
'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(<YourSta
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]
<YourIncrementNumber> is optional. Default is one.
Example: ALTER TABLE [Table1] ALTER COLUMN [AutoNum] COUNTER(300,5);
4) Menu>>Query>>Run
That's it.
mx
********************