Bill Benson
bensonforums at gmail.com
Sun Dec 28 00:05:08 CST 2014
Steps 4 and 8 were the ones I meant had to do with updating values. On Dec 28, 2014 1:03 AM, "Bill Benson" <bensonforums at gmail.com> wrote: > Some of what I wrote responded to Charlotte, however you did lead off > with "I'm with Charlotte." Jim! By the way, Charlotte wrote: > > "all [the] relationships have to be updated". I think that is technically > inaccurate, perhaps she meant resequencing would require updating values in > related tables. (Charlotte, if not clear, that's the purpose of steps 4 and > 10, which the code handles pretty efficiently.) Or did you really mean > there is some aspect of the relationship I am neglecting? I hope not! > > Now I will put my "sober professional" hat on and join these sensible > colleagues in sternly advising AGAINST using my own creation. > > (1) not genuinely useful, as stated by others (2) potential to cause > irreparable damage to data if interrupted or unforeseen error occurs - > backup, backup, backup (3) probably doomed to fail in a multi user > environment, or replication projects. For sure it makes data no longer > consistent with historical records in external, integrated systems no > longer fully integrated with your database. > > I see this maybe as a method I can use in pre-release, sample data > environments to keep ID'S visually easy to inspect and observe changes more > readily. That's about it. > > All of that said... > > The amount of learning I gained by this exercise was significant, whiefly > with respect to the DAO object model, and it was fun making use of a bit of > learning gained here on this list with respect to auto umber field values > "tripping" when a certain high value is reached. Very cool use of that > fact, I think I get some credit for thinking creatively, no? > > The most challenging part for me was creating valid records for insertion > into a table that is related to many tables with referential integrity set. > I solved that using the relation object model to get the foreign tables and > the foreignname of the related pk fields... then used Dmin() to get a valid > value from each table to update the temp table's dummy record (s) with real > data - otherwise I couldn't append it's records to the table I was trying > to resequence. > > Good night all. > On Dec 27, 2014 9:33 PM, "Jim Dettman" <jimdettman at verizon.net> wrote: > >> I'm not trying to rain on anyone's parade. >> >> All I asked was the question why thinking I wasn't understanding >> something. >> >> Well now that you made the why clear, well there's really not much else >> to say. >> >> Jim >> >> Sent from my iPhone >> >> > On Dec 27, 2014, at 8:29 PM, Bill Benson <bensonforums at gmail.com> >> wrote: >> > >> > ??Changing a PK means all its relationships have >> >> to be updated >> > >> > which I solved. And that was the challenge in this for me, which I >> solved - >> > something Jim you would have recognized if you looked through the code >> or >> > the notes I wrote above the routine. >> > >> > I only did such a project just to see if it could be done. As for why, >> > well, I don't like index id's that are out sequence, particularly in >> fairly >> > young databases such as the ones I am actively developing. I am a little >> > ocd, I must admit. >> > >> > No one else has to think it is useful, but it there really a cause to >> poop >> > my party? If you see something flawed in the solution, that would be >> > helpful to me. >> > >> > :) >> > >> > As I wrote in note3 >> > >> >>>> 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. >> > >> > And it was self-fulfilled prophecy on that score. >> > >> > Thanks for using butane lighters and not flame throwers. >> > >> > >> > Different strokes for different folks. >> > >> >> On Sat, Dec 27, 2014 at 7:08 PM, Jim Dettman <jimdettman at verizon.net> >> wrote: >> >> >> >> 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 >> >> >> >> -- >> >> 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 >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >