Re-structuring auto-numbers

[sudhirhinduja]

Hi all,

I have deleted some records to clean up data, so the auto-number has got messed up. Can anyone suggest ways to straighten the issue so that the auto-numbering gets continous again? The field CompID which was autonumbered was the primary key of the table and foreign key of the other table. Of course I want to maintain the relationship also

I'm a newbie to VB so I don't know much on VB.

Thanks,

 

[sfreeman@co.mer]

that is correct, but if you simply must number in sequence...

Do this only on a backup copy of your data to make certain it works as expected.

Assuming one primary and related secondary table...

Delete the relationship between the tables.

Open the primary table in design view and add a new field beneath the autonumber field called OldValues, number type.

Save the table, open it in datasheet view, and select the entire original autonumber field, and copy the data, and paste it into the new field. Make sure that every record has copied the original autonumber value into the new field.

Open the primary table in design view and delete the autonumber field. Save the table. Re-open in design view and add the autonumber field back again. Save the table.

You should now have a new autonumber field with all sequential valeus, and the new number field that contains all of the original autonumber values.

Create an update query to change the value in the secondary related table foreign key field to the new autonumber values, where the foregin key value equals the old value.

Re-create the table relations, and test to make sure that you have no orphaned records in the secondary table.

Simple. ;-B

 

[sudhirhinduja]

Thanks

Thank you,

I shall try it out. Pat, I also considered your opinion. I shall get back on the issue.

Thanks once again.

 

[sudhirhinduja]

Well Pat,

I think that I shall not need to fix the auto-numbering problem as they are now planning to carry forward the previous numbers. Thanks for all your advice.