[AccessD] Access and SQL Server

In fact I do that.  It establishes the PKID and it is there when I need to use that as a FK in 
another table, which is more often than one would think.

If I need a table, I need a autoincrement PKID.

John W. Colby
www.ColbyConsulting.com

On 3/1/2011 1:40 PM, Jim Dettman wrote:
>
>   So on a many to many linking table you would do this:
>
> tblBooksAndAuthors
> LinkID - Autonumber - PK
> AuthorID - Long - FK to tblAuthors - CK-A
> BookID - Long - FK to tblBooks - CK-B
>
> And not simply:
>
> tblBooksAndAuthors
> AuthorID - Long - FK to tblAuthors - PK-A
> BookID - Long - FK to tblBooks - PK-B
>
>   and eliminate an index?  If so, why not?
>
> Jim.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, March 01, 2011 12:47 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [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.)
>>
>>