Firstly, I find your use of "Eh?" offensive. Your MVP status does not exempt
you from the rules of simple courtesy...
By hard-coded joins I mean joins made using a custom ID field: look at the
sample Northwinds .mdb join: Cutomers-Orders, which uses a custom ID field,
CustomerID, which is not an autonumber field.
Also, the following article excerpt is relevant:
Teach your Access users to be wary of AutoNumbered primary keys
Feb 5, 2002
Peter Nelson
© 2002 TechRepublic, Inc.
As an active Microsoft Access developer, I've got a good view of one side of
the database development environment. But as an active Microsoft Access
instructor, I also get to see what the rest of the nontechnical developer
world is doing with Access. It's this latter view of Access that has
revealed how common it is for nontechnical Access users to incorrectly use
an AutoNumber field type as a table's primary key.
Here is why it's so important to steer end users away from this dangerous
practice.
Ignorance is rarely bliss when it comes to Access
Most business users, or even junior developers, do not have an in-depth
understanding as to what a primary key is, let alone the ramifications of an
ill-conceived primary key selection.
When a user saves a new table in Access, a dialog box pops up asking if the
user wants to create a primary key if one doesn't exist. The message says,
"Although a primary key isn't required, it's highly recommended. A table
must have a primary key for you to create a relationship between this table
and other tables in the database. Do you want to create a primary key now?"
Many of the users I've talked to in introductory, intermediate, and
sometimes even advanced Access classes usually say, "I saw the message and I
figured, what the heck, seems like a good thing to do." So, while they are
actually taking the right step in creating a primary key, they don't know
how to correctly complete the task.
A simple plan
Poorly designed applications are usually more cumbersome and costly to
maintain than those with the benefit of good initial planning.
Well-designed databases are generally characterized by a group of tables
storing related data that is joined together through keys. For example, a
database that stores information on customers and their related orders would
likely have two tables: Customers and Orders. The Orders table would not
contain any information about an order's related customer (address, phone
number, and so forth). Instead, it would contain the key that identifies the
row containing the customer's information in the Customers table.
AutoNumber: A double-edged sword
When choosing a key, it's generally a bad idea to choose a field that can be
edited by a user. Doing so forces you either to restrict the user from
editing the field after the record's creation or to provide a way of
detecting and correcting key collisions. If you restrict the user from
editing the field, you may discover that your application isn't flexible
enough. The second choice is problematic as well; providing for the
detection and correction of key collisions can be too complicated, seriously
hindering your application's performance.
The AutoNumber datatype offers a handy solution to this problem but not
without making your application more vulnerable to failure. On the positive
side, using the AutoNumber datatype provides a field that will give you a
unique value for every record, while also paving the way for establishing
relationships between multiple tables. The negative side is that the
application stands a much better chance of failing if the AutoNumbered
values become corrupt.
Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:uo********************************@4ax.com...
"Tony D'Ambra" <td*****@swiftdsl.com.au> wrote:
The issue of using AutoNumber keys in joins is problematic for the reasons
outlined. You should use hardcoded id's for joins to avoid the issue
altogether...
Eh? I've been using autonumber primary keys in all my systems since the
first one I
created in A2.0 using natural keys. Thus the autonumber keys are present
in all the
joins. Or am I misunderstanding something?
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm