Bill,
Lesson learned on using the auto number as a meaningful value. I am now
thinking it may be easier to make that change in this data base now.
I may change my auto-number to number, with no duplicates, and keep it as my
field that the relationships are built on. It will still function fine.
Then if I put in a new Primary key it may not matter if it is the same as it
will not be meaningful.
I will bring all this home tonight and work on it.
Thanks,
Bill
Bill Singer
A.T.Group
2011 & 2012 Five Star
Benefit Professional
877-902-8898
<mailto:Bill.Singer@at-group.net> Bill.Singer@at-group.net
cid:image006.gif@01CBA804.B4154720
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
Sent: Friday, April 06, 2012 11:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: New primary key
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
<mailto:MS_Access_Professionals%40yahoogroups.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
<mailto:MS_Access_Professionals%40yahoogroups.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]
>
_____
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2409/4918 - Release Date: 04/06/12
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar