[AccessD] Resequence All Autonum Fields - a tentative approach

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.