reorder auto increment

[cc96ai]

MsAccess 2003,
we set primary key as AUTO INCREMENT,

e.g .
ID :
1 , 2, 3, 4, 5, 6, 7, 8, 9, 10

once we deleted 4,5,6
will become
1 , 2, 3, 7, 8, 9, 10

have any way that we can reorder the ID again ?
become
1,2,3,4,5,6,7

any suggestion ?

or we MUST retrieve the data and insert back into a new table ??

 

[Guest]

Hi

That is not what an autonumber is for. Access requires that each record has
a unique identifier - in fact in most case the record "is" the indentifier

There may be twins at the same address - same date of birth - and (stupid
parents) the same name. You need a way of identifying one from the other -
this is what the autonumber will give you.

You should not us it for anything else.

You can always use
Me!PrimaryField = DMax("[PrimaryField]", "[TableName]") + 1 if you really
want to have a consecutive numbering system - but don't use autonumber for
this.

The Me!ID = DMax("[PrimaryField]", "[TableName]") + 1 can be used
BeforeInsert on a form to generate the next available number.

Hope this helps

 

[cc96ai]

I totally understand,
I joined in the middle of project, and the client want to do it,
thats why I have this kind of problem ,


Thx for all the advices

 

[John W. Vinson]

I totally understand,
I joined in the middle of project, and the client want to do it,
thats why I have this kind of problem ,

Ask the client:

Here's a scenario. You've been using the database for a few years. You have
328,451 records.

You realize that record 3 was a test record which should not have been left in
(or it was entered incorrectly, or it's really a duplicate of Record 2, or any
other reason) and must be deleted.

Do you want Access to renumber all records from 4 to 328451?

Should that renumbering be applied to all the printouts that have been
generated over the years?

Should the minds of all users of the database be edited to decrease the record
number of their most important piece of information by 1?

Autonumbers are JUST arbitrary unique identifiers. If you use autonumbers, you
will have gaps.

John W. Vinson [MVP]