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
|
|||
|
|||
Changing a number field to Autonumber
What is the easiest way of amending a Field in a table that has thousands of
entries in it to be Autonumber? I have managed to copy structure and amend an empty table to have autonumber but cant figure out how to copy the records from original table into the copy with amended field? And finally would a renaming of new table to old table name knock out all queries and reports? Any advice would be appreciated Glenn |
#2
|
|||
|
|||
Changing a number field to Autonumber
"Pitlochry1" wrote in message
... What is the easiest way of amending a Field in a table that has thousands of entries in it to be Autonumber? I have managed to copy structure and amend an empty table to have autonumber but cant figure out how to copy the records from original table into the copy with amended field? And finally would a renaming of new table to old table name knock out all queries and reports? Make an empty copy of the table. Create the autonumber field in it. Use an append query to copy the records from the old table. The a/n field will populate automatically. Your queries should be OK provided that you don't open them whilst a source table is not present. Keith. www.keithwilby.co.uk |
#3
|
|||
|
|||
Changing a number field to Autonumber
You state that you are changing a number field to autonumber.
This sounds very dangerous. If you are using the original number field as a foreign key in another table then the action you are contemplating could destroy the validity of your database. It is very unlikely that the value of your new autonumber will by the same as the old number. If that happens then the subordinate records will connect to a different primary record. (i.e. Customer A's purchases will be reassigned to Customer B.) It is okay to change the field only if the number is not being used elsewhere. If it is being used elsewhere then it is still possible to accomplish the objective but the methodology is far more complex than creating a new table and appending the original records. Just be careful! Jack Cannon "Pitlochry1" wrote: What is the easiest way of amending a Field in a table that has thousands of entries in it to be Autonumber? I have managed to copy structure and amend an empty table to have autonumber but cant figure out how to copy the records from original table into the copy with amended field? And finally would a renaming of new table to old table name knock out all queries and reports? Any advice would be appreciated Glenn |
#4
|
|||
|
|||
Changing a number field to Autonumber
On Wed, 25 Feb 2009 07:39:02 -0800, Jack Cannon
wrote: This sounds very dangerous. If you are using the original number field as a foreign key in another table then the action you are contemplating could destroy the validity of your database. It is very unlikely that the value of your new autonumber will by the same as the old number. If that happens then the subordinate records will connect to a different primary record. (i.e. Customer A's purchases will be reassigned to Customer B.) Actually an Append query will append a Long Integer field into a newly created autonumber field, preserving the value. The autonumber seed will end up being one more than the largest existing value. It's not something I'd recommend doing often, but it can in fact be done safely. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Changing a number field to Autonumber
Thanks John,
I never knew that would work. Jack Cannon "John W. Vinson" wrote: On Wed, 25 Feb 2009 07:39:02 -0800, Jack Cannon wrote: This sounds very dangerous. If you are using the original number field as a foreign key in another table then the action you are contemplating could destroy the validity of your database. It is very unlikely that the value of your new autonumber will by the same as the old number. If that happens then the subordinate records will connect to a different primary record. (i.e. Customer A's purchases will be reassigned to Customer B.) Actually an Append query will append a Long Integer field into a newly created autonumber field, preserving the value. The autonumber seed will end up being one more than the largest existing value. It's not something I'd recommend doing often, but it can in fact be done safely. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Changing a number field to Autonumber
On Wed, 25 Feb 2009 09:55:01 -0800, Jack Cannon
wrote: It's pretty obscure and I remember learning it here, being surprised that it COULD work, and finding it pretty useful for fixing messed-up databases! So I thought it worthwhile to pass it on. Thanks John, I never knew that would work. Jack Cannon "John W. Vinson" wrote: On Wed, 25 Feb 2009 07:39:02 -0800, Jack Cannon wrote: This sounds very dangerous. If you are using the original number field as a foreign key in another table then the action you are contemplating could destroy the validity of your database. It is very unlikely that the value of your new autonumber will by the same as the old number. If that happens then the subordinate records will connect to a different primary record. (i.e. Customer A's purchases will be reassigned to Customer B.) Actually an Append query will append a Long Integer field into a newly created autonumber field, preserving the value. The autonumber seed will end up being one more than the largest existing value. It's not something I'd recommend doing often, but it can in fact be done safely. -- John W. Vinson [MVP] -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|