If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
New Auto Number field needs to take place of old number field
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 |
#2
|
|||
|
|||
On Tue, 28 Jun 2005 23:05:40 -0400, "Scott B"
wrote: 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] |
#3
|
|||
|
|||
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 "John Vinson" wrote in message ... On Tue, 28 Jun 2005 23:05:40 -0400, "Scott B" wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Scanner in Access | GX | General Discussion | 1 | May 5th, 2005 05:26 PM |
Combo Box & Text Box | AccessRookie | Using Forms | 3 | April 6th, 2005 11:33 PM |
MAX() operator with auto number field | kasab | Running & Setting Up Queries | 1 | March 27th, 2005 12:47 AM |
Number of objects | thomak | General Discussion | 3 | February 17th, 2005 11:58 AM |
query a number stored as text | Lee | Running & Setting Up Queries | 19 | October 13th, 2004 04:10 AM |