[AccessD] Resequence All Autonum Fields - a tentative approach

On Sat, Dec 27, 2014 at 3:23 PM, Bill Benson <bensonforums at gmail.com> wrote:

> I have written this routine to autonumber field in the table.
> If someone wants to kick its tires and see if it handles their complex
> database, great - but I suggest making a backup (perhaps more than one!).
>
>
https://drive.google.com/file/d/0B-Klb4AlrC9JaExTV1p3d2JBSXc/view?usp=sharing


> Order of steps:
> 1) checks the max PK value, make sure there is room to add all records
> again without hitting
>    Const HIGHEST_POSSIBLE_VALUE = 2147483647
>
> 2) Preserve indexes in a table, then delete them
>
> 3) Duplicate records so that there are equivalents in a higher range
>
> 4) Swap all values appearing in related tables that reference this table's
> PK in their foreign key
>    I am not sure if I handle multi-column FK references appropriately.
>
> 5) Delete the predecessor records from the table
>
> 6) Seeds in HIGHEST_POSSIBLE_VALUE = 2147483647 to the table; seed in 0
> to the table;
>
> 7) duplicates all records again, reusing the range 1, 2, 3 ... for PK
>
> 8) Swap all values appearing in related tables that reference this table's
> PK in their foreign key
>    I am not sure if I handle multi-column FK references appropriately.
>
> 9) Remove the no longer needed higher PK range of duplicate records
>
> 10) Replace indexes
>
> Note1: The routine only works on Fields / Foreign Keys that are long
> integer, and are autoincrementing (it tests).
>
> Note2: No doubt others have created even more robust routines, this was my
> 1st attempt.
>
> Note3: I will get flamed as creating something that serves no legitimate
> purpose in the database. Oh well, everyone is entitled to their own set of
> principles.
>
> Note4: If the routine pauses with errors in the midst - that is for
> debugging purposes; don't count on your database maintaining state... I
> would fix the code and start again from backup.
>
> Note5: There will be bypassed errors when indexes are stored and
> re-created for those items which are bound up in relationships.
>
>