[AccessD] Resequence All Autonum Fields - a tentative approach

Bill Benson bensonforums at gmail.com
Thu Jan 1 14:34:03 CST 2015


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 <mailto: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 <mailto: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 <mailto: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 <mailto: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 <mailto:bensonforums at gmail.com> > wrote:
>>>>
>>>> On Sat, Dec 27, 2014 at 3:23 PM, Bill Benson <bensonforums at gmail.com <mailto: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 <tel: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 <tel: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 <mailto:AccessD at databaseadvisors.com> 
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com <mailto:AccessD at databaseadvisors.com> 
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com <mailto:AccessD at databaseadvisors.com> 
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com <mailto:AccessD at databaseadvisors.com> 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com <mailto:AccessD at databaseadvisors.com> 
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list