How can we rearrange the Autonumber

[Frank Situmorang]

Hello,

I am creating database to replace my excel. I imported all excel data while
I am constructing the database design.

In the middle of the road I made a testing to see the result after it is ok
I deleted the record and we see missing numbers

Here I illustrate the problem
Actual data:
REcord No.( auto) Membername

1 Jason
2. Mary
....upto 580
Testing data
581 to 584....I delelted.

Continued actual data
585 Guru

What we want is actual data should still continue on 581.( REcord number)

My question how can I do it. Can I export 1st all data and purge the
database then import the data? or is there any way. How can we do it for all
tables. I think other table also should be evacuated.

Thanks in advance
Ist

 

[gllincoln]

Hi Frank,

First of all - important for you to understand that autonumber fields are
not intended to have meaning. You are assuming they should be consecutive,
no missing numbers. That assumes that the number has an intrinsic meaning by
way of its serialization - and that is not expressedly true by design. That
list could be 1,10,100,105, 195867, 456, 3 and it would work just the same.
In fact, the old replication ID's were long randomly assigned numbers,
similar in design and function to the GUID values. They are just unique -
and that is all that is important about them.

If you need a way to visably see what row was added first - use a timestamp
value - the autonumber is value whose sole purpose in life is to serve as a
unique identifier for that particular row. If you delete the row, the reason
for that particular value's existence has been eliminated. Reproducing the
deleted row id values can be harmful, for instance - if you have related
'child' records, then the related records would now be pointing to a 'wrong'
parent row.

However, yes it can be done - if you insist on doing this in spite of being
told that it's not a good idea... By the way, I do understand that
sometimes the workplace creates irrational demands. For example - the boss
sometimes just doesn't get it - and wants his numbers all tidy, neat, in a
row, as if he was numbering a list on a piece of paper.

Here is one way to do it. You can create a second identical table, excepting
that is has an ID column that is just a plain integer - not an autonumber.
You can copy the old table into the second table. You can change the ID
column values to suit yourself. Fill in the gaps, whatever. Then delete the
original table, and create an append query to stuff the contents of your new
table into the old table.

The interactive datasheet view and the form view won't let you edit the
auto-number bound field - SQL will just shove what you have in there - as
long as the data type is compatible. (auto-numbers are long integers).

This also has the effect of resetting the autonumber 'counter', sometimes it
behaves logically, other times it's a little flaky about it. If you have
'filled in all the gaps' then it will usually start up at the next available
number. If you do not fill in the gaps, sometimes it will start at the first
gap and backfill. This can do funky things to related rows - if you
experience other problems as the result of doing this, let me be the first
to say, "I warned you that this isn't a great idea."

Hope this helps,
Gordon

 

[Frank Situmorang]

Okay Gordon, I will give a try

Thanks very much