Crystal, Bill
If the AutoNumber field is used as a foreign key in any other table you could turn your database into cottage cheese. At the very least, there would be a lot of clean up to do.
I'd :
1. change the AutoNumber to a Long
2. change all relationships to cascading updates
3. Fix the numbers in the parent table which will cascade to the child tables
4. Append all fields in the parent records to a new table with an auto number.
5. Drop the relationships on the old table and delete it
6. Rename the new table and set up relationships again.
The lesson here is DO NOT USE AN AUTONUMBER AS A MEANINGFUL VALUE. And never expose it to the users. Using another field with a Long data type would be much smarter.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca
--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> Hi Bill,
>
> you can add records to a table with defined values for AutoNumber using an Append Query.
>
> If you want AutoNumbers back:
>
> 1. break relationships on current table.
>
> 2. make a copy of the table with no records.
> 3. change field to AutoNumber
> 4. set relationships
>
> 5. append records
>
>
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
>
> ________________________________
> From: Bill Singer <Bill.Singer@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Friday, April 6, 2012 9:38 AM
> Subject: [MS_AccessPros] RE: New primary key
>
> I just want to make sure this is possible.
>
>
>
> I have table with a primary key, about 300 records. The number of the
> Primary Key was important as the data was transferred from an excel
> spreadsheet and the primary key relates to other storage records. After
> the table was finished a few of the records were deleted. Now the user
> wants those Primary Key numbers back.
>
>
>
> I know I cannot just add those numbers in because they are automatically
> generated. I have changed the Auto-Number to Number, add in the existing
> records. All of that works but I cannot change the field back to
> Auto-Number, which I understand.
>
>
>
> So, I keep the table sorted by the old primary key number with the missing
> records added, I add a new primary key auto-number field and let it
> re-sequence and for some reason it does not sequence in the correct order.
>
>
>
> Is there a way to get the new primary key auto-number to sequence in the
> same way that the old primary key was sequences? The auto number value is
> what the rest of the relationships are built on and they will all be wrong
> if the sequence is not the same.
>
>
>
> Thanks for your help.
>
>
>
> Bill
>
> MN
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar