Selasa, 10 April 2012

Re: [MS_AccessPros] RE: New primary key

 

Yay!

Bill

--- In MS_Access_Professionals@yahoogroups.com, "Bill Singer" <Bill.Singer@...> wrote:
>
> Bill,
>
> I thought I should let you know that the new table worked .the first time.
>
>
>
> Thanks,
>
>
>
> Bill Singer
>
> MN
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
> Sent: Monday, April 09, 2012 4:46 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] RE: New primary key
>
>
>
>
>
> You're welcome, Bill. I hope all works out the first time around.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
> <Bill.Singer@> wrote:
> >
> > Bill,
> >
> > I did as you said a few e-mails below. I created a new table. Appended the
> > data into it from the old table and the Primary Key re-sequenced. Perfect.
> > Now I just have to rename a few things and verify that it works.
> >
> >
> >
> > Thanks,
> >
> > Bill
> >
> > MN
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill Mosca
> > Sent: Friday, April 06, 2012 12:44 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] RE: New primary key
> >
> >
> >
> >
> >
> > Bill
> >
> > You should never build a relationship on anything but the primary key.
> >
> > And also know that you can have only one AutoNumber per table. If you
> choose
> > to use a Long instead you will have to provide a snippet of code to get
> its
> > value using DMax.
> >
> > Private Sub Form_BeforeInsert()
> > txtMyRecNumber = Nz(DMax("MyRecNumber", "MyTable"),0) +1
> > End Sub
> >
> > The above will check the table for the highest MyRecNumber and add 1 to it
> > for the new record.
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
> > <Bill.Singer@> wrote:
> > >
> > > 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
> > >
> > > >
> > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> Mosca
> > > Sent: Friday, April 06, 2012 11:13 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.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>
> > <mailto:MS_Access_Professionals%40yahoogroups.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>
> > <mailto:MS_Access_Professionals%40yahoogroups.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]
> > >
> >
> >
> >
> > _____
> >
> > 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]
> >
>
>
>
> _____
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2411/4925 - Release Date: 04/09/12
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar