renumbering autonumber 1

[tatika]

My table's primary key is an autonumber. There are more than 1000 record in this table.
Is there a way to renumber all my records?

Thanks

 

[scriverb]

Why are you worked about doing this? Is it that you want to fill in the blanks? Command misconception about the AutoNumber field. This field should just be used as a unique identifier for your record. A pointer if you will. Don't worry about the fact that there are missing number due to canceled new records or deleted records.

There is a way to do this but why don't you first post back with why you want to do it?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[tatika]

Sorry my computer went crazy...
The reason why I would like to renumber my table is to fill in the missing numbers. There are too many records missing....
thanks,

 

[scriverb]

Yes, but what I am trying to get you to understand is that these numbers will always get out of wack. they are there as just a record identifier and really shouldn't be visible to the user. The programmer should be using them in the background to link tables together, to find particular records, etc. The fact that there are blanks has no real significance except it has a tendency to drive new ACCESS programmers batty. But, if you want to renumber then the best way to do that is the following:

1. In the tables tab. Select your table(yourtable) and right click Copy. Then right click Paste.
2. In the prompt box Paste Table As window select Structure Only. Enter the new name for the table(yourtable) and click OK.
3. Now run the following SQL to read the records from yourtable and append to the new table yourtable1.

INSERT INTO yourtable1 ( fieldname1, fieldname2, fieldname3 )
SELECT A.fieldname1, A.fieldname2, A.fieldname3 
FROM yourtable as A;

Update the table names and the field names. Make sure to include all of the field names in the tables EXCEP the autonumber field.

4. Now delete the old table and rename the new table to the first tables name.

You now have removed all of the blank records.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[MikeRBS]

If missing numbers worry you, then autonumber is just going to give you ulcers. It's always creating gaps - often big ones - in my applications.

 

[tatika]

thanks a million

 

[lespaul]

tatika,

You do realize that even though you now have nice incremented numbers, as soon as you delete a record, you will have another gap?

leslie

 

[willir]

As an FYI...

Your "missing numbers" are probably created when the end user goes to create a record and then hits the escape to cancel out.

Access grabs the next autonumber as soon as the end user starts entering data into a new record. (Autonumber field displays a number) When the ESC key is hit, the caption in the field resorts back to autonumber ... but the system table that stores the autonumber has already advanced to the next number.

Try and treat the auto number as a cosmetic thing; the sole purpose of the autonumber is to uniquely identify a record without having to develop a huge amount of programming code.

As Leslie so eloquently states it, you will be running into a maintenance headache if you try to use the autonumber to track sequential records. We use the "Select count..." for this purpose.