Jim Dettman
jimdettman at verizon.net
Sat Dec 27 18:08:40 CST 2014
I'm with Charlotte; what's the problem your trying to solve? Jim Sent from my iPhone > On Dec 27, 2014, at 6:44 PM, Charlotte Foust <charlotte.foust at gmail.com> wrote: > > Sorry, but why do you even want to do this? Autonumbers aren't > necessarily sequential. So what? If you need sequential add a surrogate > key and make it sequential. Changing a PK means all its relationships have > to be updated > > Charlotte >> On Dec 27, 2014 12:45 PM, "Bill Benson" <bensonforums at gmail.com> wrote: >> >> 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. >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com