[AccessD] Resequence All Autonum Fields - a tentative approach

Bill Benson bensonforums at gmail.com
Sun Dec 28 00:03:55 CST 2014


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