Bill Benson
bensonforums at gmail.com
Thu Jan 1 16:08:04 CST 2015
I have updated the solution. I still consider it a work in progress and I will still report under this thread any progress - which I ask that those who are not interested just ignore, I am not going to assume that just Jim and Charlotte responded, and they truly don't care about the solution, that they represent everyone in this community. So if you really want me to stop posting updates, then everyone on the list (and I will get the admin to tell me if we do or don't hear from everyone) to write in saying "No more updates"... heHeHEE. https://drive.google.com/file/d/0B-Klb4AlrC9JaExTV1p3d2JBSXc/view?usp=sharing The new revision now adds a column to the temp table, and adds in that field on any record of the temp table, a valid value based on field type - for any leftover required fields in the table whose autonumber field is being reseeded. The fact that trying to add every required column overlaps with some columns that were added already after the relationship testing is unimportant, I just drop the item from the list of additional fields to add after getting runtime error 3880. I actually attempt to add a field that was already added by virtue of it having been formerly found to be in relationship - but I just let the attempt fail with error number 3380. Without also adding these columns to the temp table - and a valid value for any records in the temp table - then the append query from temp to the main table cannot be completed. I think there is still one more step which I shall have to investigate sooner or later, and that is, any fields in the primary table which are RELATED to other fields in the database but which are NOT of type Long. The current and former "release" of this solution are modeled after my own databases in which I do not relate fields which are not type Long and/or autonumbers. So if you are playing with this, watch out for that. On Thu, Jan 1, 2015 at 3:34 PM, Bill Benson <bensonforums at gmail.com> wrote: > I ran into my first problem with the method – which I think I can overcome > when I get time. I so far am only adding columns to Temp, and inserting > into Temp valid values from fields in the table whose autonumber field is > to be reseeded, which was proven to be in relationship to a field in > another part of the database - or which is part of the primary key. Well I > just encountered the first “miss” – I need a valid value also in any record > in Temp which is a required field in the table whose autonumber field is to > be reseeded. I don’t think I can get to fixing this until 2015. Wait… > > > > > > > > *From:* Bill Benson [mailto:bensonforums at gmail.com] > *Sent:* Sunday, December 28, 2014 1:04 AM > *To:* Access Developers discussion and problem solving > *Subject:* Re: [AccessD] Resequence All Autonum Fields - a tentative > approach > > > > 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 > >