Import Autonumber indexed records into a new table

[Jean9]

I have a database which I am unable to compact and repair as this action causes cascading issues with the user forms and functionality. So everytime the user has run into a record issue, we have just restored the database from a previous backup. Ideally, the database needs to be repaired. There is a record in a single table that reads "Deleted" for all columns. I have tried a manual delete of this record, which the delete message does display on and the delete is confirmed, however, subsequent access to this table shows that same "Deleted" record. Only a compact and repair will get rid of this but as previously stated, causes other issues. So, that being said, I would like to try to copy the data out from the table into a clean table but the original table's index is an autonumber so I don't know how to copy the data to another autonumber indexed table while still keeping the original autonumber value as this field is referenced as an alternate key in other tables.

Any ideas for getting rid of this "deleted" record or copying the data out into another table?

Thanks,
J9

 

[Remou]

It should be possible to copy the data to a new autonumber field. The original numbers will be kept.

 

[Lhuffst]

I have a similar problem. The key field is an autonumber and 2 of the numbers have come up with an error. I know the correct numbers they should be but cannot make changes to the column. I changed it to a number and made the change but then it will not let me put it back on autonumber. Any suggestions?
lhuffst

 

[Jean9]

The copy thing never did work. I ended up having to VNC onto the user's machine to do the actual compact and repair so as to keep the compact and repair using the office version on my machine from messing up how the office version on their machine handles the data.
j9

 

[JBinQLD]

lhuffts: If you have a lot of data in the table then this may be an option for you: It looks convuluted but only cos I've broken it into simple steps for ohers future reference...

1 - BACKUP your data first!

2 - Convert your corrupted autonumber to number (long) and fix the problem records (As you've done)

3 - create a New field called "NewID" and set to Autonumber

4 - Create a query and add your 'Corrupt' table and the child table

5 - Create a link link between your 'fixed' long field and the FK in the child table.

6 - Add the FK field to the output of the query

7 - Change the query type to "UPDATE" query

8 - In the "Update To" field of your only output enter something like [CorruptTableName].[NewID]

9 - Execute the query

10 - Discard the query and open your 'corrupt' table in design mode. Discard the previous corrupt and now 'long' field

11 - Rename the NewID field to what it shoud be called

NB. You will probaably have to delete the relationships in before you can delete the old field but once you've renamed the new one you can go and re-create them.

Take it easy, JB