change auto number

[steve111]

before ACCESS Excel was used to create orders etc,

we are up to order number 16223
now I have access and the auto number will start at 1
how can I get the auto number to start at 16223

steve

 

[Minty]

Come on Steve, I know you are new to all this but please try a bit to find the answers first
http://allenbrowne.com/ser-26.html

 

[pr2-eugin]

What you need to do is something called seeding. It is a bit of a painstaking process. The outcome you anticipate by doing so is not a guarantee. I would suggest you first read this, Autonumbers. This will give you an idea of what are autonumbers and what they are not.

Then, what you need to do is, create about 16223 dummy records, (could be easily done with code) and delete them. Then start your normal process of adding records. You should be having the next ID 16224 (hopefully).

 

[smig]

create an excel file with 16223 records and copy paste the rows into the Access table.
delete these records.

 

[bob fitz]

Steve

IMHO auto numbers should not be used for meaningful data. Trying to do so will lead to problems.

 

[steve111]

hi
thanks

I never thought about coping 16000 records
but that's what I will do

do not understand code

but thanks to all

steve

 

[ButtonMoon]

Then, what you need to do is, create about 16223 dummy records, (could be easily done with code) and delete them. Then start your normal process of adding records. You should be having the next ID 16224 (hopefully).

Fortunately there is a much easier alternative. Just run the following query:

ALTER TABLE Table1 ALTER COLUMN id AUTOINCREMENT (16224,1);

 

[bob fitz]

Steve
Did you read the AutoNumbers link provided by Paul in post#3

 

[gemma-the-husky]

Steve

I assume in excel you are using a formula "last row"+1 to get the new order number

OK - to do something similar in access, you do not want an autonumber. You want to use a "dmax"+1 type solution. (or even better, get the next number from a different table altogether.

 

[spikepl]

Interesting how many complex soultions there can be.

In an append query you can append a record to the table in question, including a value in the autonumber column (whoich no already exists) . The next autonumber the next one in the sequence. No need to copy thousands of records.

 

[Minty]

We use autonumber's (actually SQL Identity fields) as unique ID's such as call numbers and account numbers, delivery site ID's etc, although they are technically end user visible, they are only a unique, not trying to represent anything more specific.

In this scenario why are they apparently frowned on? I can't see any difference between this and a dmax +1 type generated number. As long as it's unique(and it has to be) I don't care if it's out of sequence or has a gap.

Oh and I agree with spikepl about complicated ways to achieve this. Just append a record with a ID no 1 less than required start number before setting the field to be an autonumber.

 

[ButtonMoon]

We use autonumber's (actually SQL Identity fields) as unique ID's such as call numbers and account numbers, delivery site ID's etc, although they are technically end user visible, they are only a unique, not trying to represent anything more specific.

In this scenario why are they apparently frowned on? I can't see any difference between this and a dmax +1 type generated number. As long as it's unique(and it has to be) I don't care if it's out of sequence or has a gap.

There's nothing necessarily wrong with using an autonumber/IDENTITY to generate a "natural" key value for your business data. It's technically a superior method to taking the max + 1 of a column because it doesn't cause the same blocking/serialization problems.

There are potential limitations if you use autonumber/IDENTITY column as a natural key (e.g. account number, delivery number, invoice number) within a table however. You can't easily update the column, which is a potential disadvantage once data actually becomes a meaningful part of the business process - data quality issues do happen. Also, both SQL Server and Access only permit one such column per table, which means that if you use that column as a natural key then you can't also use the same mechanism for a surrogate. That's not necessarily a disadvantage if you didn't find the need to have a surrogate key anyway but IDENTITY/autonumber is generally designed and assumed to be used for surrogate keys.

In SQL Server a Sequence is generally a more flexible option for a key generator instead of using IDENTITY. Sequences can generate values for you, you have more control over them, you can put them in updateable columns and there are no special restrictions on how you use them.