Nightmare crash - need help with autonumbers

[ProgramError]

Hi all

We recently had a backend data corruption. Luckly we kept backups but not that frequently as we should have. We have managed to restaore all the records back to there original state apart from problem we are having changing a long integer field into back into a auto number field.

Let me explain a little more.
the corruption caused records to be duplicated and the key field number to be out of sequence
a simplistic example would be ...
1 2 3 4 5 6 7 8 9
suddenley became
1 2 4 5 6 2 3 7 8 9
and because of this we could not delete the problematic records unles the key field was changed from a autonumbering field to a normal long integer field.

can anyone advise how to change it to a autonumbering field?

p.s. the numbers already allocated to the records need to be kept as they are, so sequencially renumbering them is out of the question.

Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[dRahme]

Hi, I am not aware of a real simple way but I guess what I would do is delete the problematic records from the original table and change the Autonumber field to general number and then copy your problem table to another table, structure only.

Change the ID field in the new table to Autonumber. Append enough records to encompass the the original autonumber range in you old table to the new table.

Create a query, include the new table and the old table and join on Autonumber where both fields are equal.

Update the new table fields to the original table values and then delete the records where there is no Autonumber match.

I would think that would do the trick.

Hope that helps

 

[BoxHead]

Ian,

I don't believe you can change a field to autonumber after a table has any data added to it. I think you'll have to add a ew autonumber field synched to the old one and delete the old Id field.

You can use the ALTER TABLE method to add the new autonumber field, but before you do that, you'll have to make sure that the table is ordered by the old ID field and that there are no gaps. If your last record's ID is 10,000, you have to have 10,000 records in the table before adding the new field.

I'd run a query to see if there are any gaps in the numbering and add dummy records to fill in those gaps. Make sure that the dummy records contain some off-the-wall value in a specific field so you can find them easily for deletion later.

Access will probably think that the dummy records added should be at the end of the recordset so once you have your 10,000 records, run a make table query to create a new table ORDERED by the Id field.

Use ALTER TABLE myNewTable ADD COLUMN myNew ID COUNTER; as your SQL to add the new ID and then delete the dummy records and delete the old ID field.

Switch the table names and you should be back where you started.







John

Use what you have,
Learn what you can,
Create what you need.

 

[ProgramError]

Thanks dRahme and boxhead

from what you are saying it sounds as though I need to make a new table with sequencial numbers in the autonumber field and copy across the data with the matched id number to the newtable and than delete the empty records. copying and pasting the record with corrupt duplicate information to be add in their correct position afterwards.

How would I go about copying data from a record in one table to the matching autonumbered record in the other table?

The way I intend doing it is....
(this is not the code but the principle)

for counter = 1 to eof
'checks to see if the next record id in the old table is in the sequence
if (record ID counter + 1) <> (counter + 1) then
put null information in the record
else
copy theinfromation from the old table
end if
next record

Will let you know if this will work. I'm sure there are people out there who may have had a similar problem, so if I can put something back into the forum for all the help I have received from it then I'll feel good.

Thanks once again.




Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[dRahme]

I would think an update query would do the trick..