New Auto Number field needs to take place of old number field

[Scott B]

Greetings,

I have an interesting puzzle for someone to unravel. A database I created
was corrupted. My backup was several days old, several hundred records. I
rescued the table data from the corrupt database. The rescued tables
originally had an autonumber field for a primary key in two large tables
(one has 625 records and one has 825 records). The rescued tables ended up
with number fields instead of Auto Number fields. I need to change the
number fields to Auto Number fields. Access says no. I have thought of
insertng a new autonumber field and deleting the old number field. But I
think that would mess up my connections to the tables. One table is a list
of guests and the other is a table of those guests' stays. The table data
is already connected to each other in relationships. If I change the
primary key field the connections to the guests and their stays will be
lost. Is this true. If so is there any solution? Or can I just create a
new Auto Number field and delete the old number field?

Thanks for the help.
Scott B

 

[John Vinson]

Greetings,

I have an interesting puzzle for someone to unravel. A database I created
was corrupted. My backup was several days old, several hundred records. I
rescued the table data from the corrupt database. The rescued tables
originally had an autonumber field for a primary key in two large tables
(one has 625 records and one has 825 records). The rescued tables ended up
with number fields instead of Auto Number fields. I need to change the
number fields to Auto Number fields. Access says no. I have thought of
insertng a new autonumber field and deleting the old number field. But I
think that would mess up my connections to the tables. One table is a list
of guests and the other is a table of those guests' stays. The table data
is already connected to each other in relationships. If I change the
primary key field the connections to the guests and their stays will be
lost. Is this true. If so is there any solution? Or can I just create a
new Auto Number field and delete the old number field?

To salvage the data *and* the autonumber values, create two new Tables
with the same design as your old tables (i.e. with the autonumber
fields) - empty.

Then run Append queries to append the salvaged data into the new empty
tables. Append the salvaged Long Integer fields into their
corresponding Autonumber fields - the data will be inserted as is,
without autonumbering.

Then delete the salvaged tables and rename the new ones. YOu'll
probably have to delete and recreate relationships to do so.

John W. Vinson[MVP]

 

[Scott B]

John,

Worked like a charm. Now if there as only a`way to recover forms and report
from a corrupted database.

Thanks for the help.
Scott B