Autonumber preservation

[Johnny]

Dear whoever-can-help,

I have a table of customers within an Access 2000 database, each with a
unique reference number (autonumber).

As this table is corrupted, I am replicating the database into a new copy
and I'm trying to take the non-corrupted records into an identical (but
blank) table in the new db.

The problem is, if I copy and paste the records over, the autonumber starts
over at "1".

Is there anyway to take the records I need into an identical table in a new
db whilst preserving the assigned autonumber references? I have in the region
of 12,000 records and only approximately 6 are corrupted. It's important that
the numbers a preserved as-is.

Please can someone point me in the right direction?

Many thanks in advance,

Johnny

 

[Allen Browne]

Use an append query to populate the new table with the fields (including the
autonumber) from the old table:

1. Attach the old table:
File | Get External | Link

2. Create a query using the linked table.
Change it to an Append query (Append on Query menu.)
Map the fields, including the autonumber.
Execute the query.

3. Delete the linked table.

 

[Johnny]

Many thanks for the reply Allen.

However, It stops and appends no records the moment it reaches a corrupt
record.

Would the best way around that to include a range in the criteria for the
autonumber field, or is there a better way?

Thanks again,

Johnny

 

[Allen Browne]

In query design view, enter criteria under the AutoNumber field.
The crucial aspect is that you must not have it read the corrupt record.

So, if you have determined that records 64 and 999 are corrupt, you would
use the criteria:
<= 63
followed by another append using the criteria
Between 65 and 998
etc. Notice how the critiera don't mention the corrupt records.

If you are not sure which records are corrupt, the partial import will be a
good clue.

 

[Johnny]

Thanks Allen. Worked a treat.

In query design view, enter criteria under the AutoNumber field.
The crucial aspect is that you must not have it read the corrupt record.

So, if you have determined that records 64 and 999 are corrupt, you would
use the criteria:
<= 63
followed by another append using the criteria
Between 65 and 998
etc. Notice how the critiera don't mention the corrupt records.

If you are not sure which records are corrupt, the partial import will be a
good clue.