[AccessD] Access and SQL Server

When I create a table in any datastore, the first thing I do is create an autoincrement PK.  I no 
longer even think about it - if need a table, I need an autonumber pk!

I then proceed to create the fields.

John W. Colby
www.ColbyConsulting.com

On 3/1/2011 12:17 PM, Jim Lawrence wrote:
> Many years ago I was taking over an Access project as the clients were
> having problems with their invoices. After about two days I discovered the
> problem with the invoice.
>
> It appears that the subform was connected to the main form by grouping
> together 3 fields, creating natural foreign key between the two tables. By
> some odd set of bad luck certain combinations of this key hash matched
> another unrelated key value and the sub form data was pulling from multiple
> invoice details.
>
> The only reliable solution was to move all the tables to auto PKs but it
> cost the client a fair chunk of change. For that reason I have never
> inflicted natural keys, on a client, no matter how strong the temptation.
>
> Jim
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: Monday, February 28, 2011 3:00 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access and SQL Server
>
> I see a lot of sense in it having a separate Autonumber PK.   This is a
> classic case of why
> you should not use a natural key as your PK.
>
> What happens when the Description changes and the existing Code is no longer
> an accurate
> short representation of Description?  Do you change it throughout all the
> tables which store it
> or do you leave your customer with strange Codes which don't match the
> description
>
> (And please don't tell me that you use Relationships with "Cascade Update"
> turned on.)
>
>