[AccessD] Resequence All Autonum Fields - a tentative approach

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
>>
>


More information about the AccessD mailing list