AutoNumber Crisis

[JumpinJimRivers]

Hello everybody,

It was just discovered that in my client's Access database there are two tables in each user's front-end that are NOT linked to the back-end like they ought to be, so each user's data for these two tables are being stored locally. This is an accident, and I need to somehow consolidate all this data back into the back-end. The problem is that the primary keys are autonumbers and the secondary key in the related table is an autonumber, so I cannot do a simple import since I am dealing with multiple tables, which means that I am dealing with duplicate autonumbers. Is there a "clean" way of handling this or am I basically screwed here?

Any help is appreciated. Thanks.

 

[MichaelRed]

basically screwed here

Although some possability exists to use one of the tables "as is" (retain it's Autonumbers) and then CAREFULLY[/color update it (append the other tables) with the other records, note -however- htat you will need to save the existing Autonumbers from the second table (another temp field), so you can use them to relate to their new counterparts to use to update the FK's in other tables.

That, is the good news.

The bad news is somewhat expressed in faq700-184, which I suggest that you rerad carefully.




MichaelRed
mlred@verizon.net

 

[JumpinJimRivers]

For now I'm just going to use excel and vlookups to fix this. Talk about a pain in the hiney. But I sure would like to know if there is a better way. Thanks

 

[JumpinJimRivers]

I just realized how this happened. I'm such an idiot, when I rolled out their last update six months ago I did an 'import' of those two tables to the back-end from the instead of linking them. Moron.

Michael, yeah that's true. That will help a little. Thanks

 

[willir]

JumpinJimRivers

(Into the deep end ??? ;-) )

Okay, here is an approach that may work...
- First realize that "autonumber" is process Access uses in (hopefully) generating a unique reference number - whether random or sequential. The "next" number is stored on an Access system table.
- Prepare and test following code
- When ready, lock the backend until you have finished the "routine" system maintenance.
- Import the two tables into the back end database.
- Did I say backup (backup and backup)
- Change the autonumbered fields in the related records to long intergers on the imported and existing tables.
- Add new long interger fields where ever you have an impacted primary or foreign key. Use relavent names so you dont get confused. Then use an update query to copy the primary or foreign key to the new field - this is a CYA.
- You may have to break your relations
- Write a program to "walk through" your imported tables and inserts them into the correct tables. Pseudo code may go something like this...

- Read first record from the master table
- Find the primary key / foreign key
- If necessary, create a record set of child records, and walk through the child record sets.
- Decide where the child record(s) and master record is to be inserted
- Use a module to emmulate the autonumbering process, i.e., DMax ("[YourPrimaryKey]", "YourTable")
- Insert the record with the correct primary / foreign keys
- Go on to the next record...

- Check a verify data.

- When complete, probably a good idea to backup again.

- Turn the long numbered fields back to back to autonumber. Several things may happen here...
-- Access may not let you convert the field back to autonumber. Not to worry. Break your relations again. Create a new field - autonumber (but not primary - yet.) This will generate new autonumbers for each record. Then use more code to update the related tables with the new number.

-- Access may allow you to assign the field as autonumber but since the autonumber is stored on a system table, the next sequential number will be wrong. One resolution may be to run compact and repair. The integrity check may reset the autonumber correctly. OR change the autonumber from sequetial to random. OR use the resolutin indicated in the first problem by using a new autonumber field.

I wish you well on this one.
Richard