?? Controlling ATOINCREMENT value ??

K

king_ita

Hi,
I would like to know the way to control an AUTOINCREMENT value of
table..
I need to do this:
when I delete a row from the table i would like to update th
autoincrement values so that there is no gap between the row before th
deleted one and the row after the deleted one.
This because I need the sequence of number to be continuous..

thanks in advance..

KIN
 
J

John Vinson

Hi,
I would like to know the way to control an AUTOINCREMENT value of a
table..

You can't. An Autonumber has one purpose, AND ONE PURPOSE ONLY: to
provide a guaranteed unique identifier for a record. Autonumber fields
will ALWAYS have gaps.
I need to do this:
when I delete a row from the table i would like to update the
autoincrement values so that there is no gap between the row before the
deleted one and the row after the deleted one.

Think about this a bit. Typically the autoincrement field will be used
as a Primary Key, as a link to other tables, which may in turn be
linked to other tables.

Suppose you delete record 3 from a table of 312,884 records, joined to
a few million related records.

Are you willing to wait while Access changes EVERY SINGLE ONE of the
other records?

Are you willing to go around and change EVERY SINGLE ONE of the places
where people have recorded the ID of an important record, on Post-It
notes, in published manuscripts, in memos, in their minds?
This because I need the sequence of number to be continuous..

Well... no. That's not its function.

If you want a continuous, gapless sequence you must a) never allow any
record to be deleted (for the reasons above) and b) write VBA code to
maintain the sequential number.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top