How can I renumber my autonumbered fields and the linked tables

[Marisa]

I have a table that after there have been several
deletions, the numbers skip, but in order to perform
certain functions, the autonumber needs to be
consecutive. This primary table is linked to at least 5
other tables. How can I accomplish renumbering all my
records?

 

[Tim Ferguson]

the autonumber needs to be
consecutive.

Autonumbers are never going to be consecutive. It's not what they are for.
If you need a record counter, you will have to program it yourself.

Try googling for "Access Custom Autonumber"

HTH


Tim F

 

[Gerald Stanley]

As you have realised, autonumber should not be used when a
consecutive serial number is required. I suggest that you
1. create a new column on your table

2. update it with a serial number using SQL like

UPDATE YourTable AS T1 SET T1.newColumn =
DCount("autoNumberColumn","YourTable","autoNumberColumn <=
" & [T1].[autonumbercolumn]);

You will have to change the items in lower case to suit
your app.

3. update all the linked tables with the newColumn value in
place of the old autonumber foreign key using SQL like

UPDATE YourRelatedTable INNER JOIN YourTable ON
YourRelatedTable.foreignKeyColumn =
YourTable.autoNumberColumn SET
YourRelatedTable.foreignKeyColumn = YourTable.newColumn;

As before, you will have to change the items in lower case
to suit your app.

4. set the new column as the primary key on your master
table and delete the old autonumber column.

5. rename the new column to the old autonumber column.

In future, you should look to incrementing this value
yourself using DMax. It would be worth looking at a thread
in the Modules, DAO and VBA section from Apr 13 entitled
'Change Autonumber Date Type'.

Hope This Helps
Gerald Stanley MCSD

 

[John Vinson]

I have a table that after there have been several
deletions, the numbers skip, but in order to perform
certain functions, the autonumber needs to be
consecutive. This primary table is linked to at least 5
other tables. How can I accomplish renumbering all my
records?

With great difficulty! Autonumbers will ALWAYS have gaps; they are
intended for one purpose, and one purpose only - to provide an
almost-guaranteed unique key.

I'd suggest:

- MAKE A BACKUP.
- Test the backup. Make sure it works! You'll be doing open-heart
surgery on this database and it might die on the operating table -
fortunately you can clone it first :-{)
- Create a new Long Integer field in the main table (which will become
the new primary key eventually)
- Create long integer foreign key fields in each of the related tables
- Open the database exclusively so nobody else can be adding records
to mess this up; then run an Update query updating the new field in
the main table to

=DCount("*", "[tablename]", "[AutoID] <= " & [AutoID])

where AutoID is your current autonumber.

- Run Update queries joining the master table to all the related
tables in turn, joining by AutoID to the current foreign key, updating
each related table's new foreign key to the new ID.
- Drop the relationships from the autonumber to its foreign keys, and
establish the new ID as the Primary Key and establish relationships to
the new foreign keys.
- Delete the Autonumber and its corresponding foreign key fields.
- Compact the database.
- Write, or obtain, VBA code to increment the new ID gaplessly. This
is not trivial code! Strictly speaking, it would require that every
higher-numbered record in the table be updated by decrementing it by
one, and every related record as well, if you should ever delete any
record. This would be VERY slow and would cause rapid bloating of your
database.

I must wonder - what functions are you running which cannot tolerate
gaps!?