On Tue, 9 Jun 2009 13:11:28 +0100, Bernard Peek
wrote:

It's not that simple unfortunately. The primary key is *always* made up
of attributes in the underlying entity and this does not include the
autonumber field. Access should identify the data field(s) as the
primary key and the autonumber field as a surrogate key. Both primary
and surrogate keys uniquely identify a record and either can be used to
implement joins.

Hi Bernard,

It seems your definition of "primary key" is narrower than mine. In
Access you can choose what your primary key is. It can be a natural
data field (or combination of them). Or it can be an AutoNumber. Or
both, although there's no real reason to do that.

The writeup at http://en.wikipedia.org/wiki/Surrogate_key is pretty
informative. We consistently use surrogate keys as primary keys, as
we feel the advantages outweigh the disadvantages.

By merging the two ideas Access makes it more difficult
than it should be to follow good practise. But that's probably an
unrealistic expectation for a tool that's designed for people with no
formal training in database design.

Well, it is designed for an easier initial learning curve. But we've
been developing custom database applications using Access, while
following good database design practices, for over 15 years. I have
no problem with Access's implementation of AutoNumbers and primary
keys.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com