On Tue, 4 Dec 2007 17:53:43 -0500, "Douglas J. Steele"
wrote:

"M." wrote in message
...

My main question is: are there negative aspects associated with using a
primary key based on data fields versus using a primary key based on an
artificial primary key as generated with an autonumber field? In both
cases
the autonumber field would be used for defining relations between tables.

There's no reason to have an Autonumber field AND a "natural" primary key.

If you're going to create relationships, they will always be based on the
primary key. You cannot change that.

As Jeff said, this really is a religious war, so I won't say any more. g

This works for me. It seems to go against what you are saying.

Sub CreateTest()
With CurrentProject.Connection

..Execute _
"CREATE TABLE Cities" & _
" (city_id IDENTITY (1,1) NOT NULL UNIQUE" & _
",city_name VARCHAR (30) NOT NULL" & _
",PRIMARY KEY (city_name));"

..Execute _
"CREATE TABLE States" & _
" (state_id IDENTITY(1,1) NOT NULL UNIQUE" & _
",state_name VARCHAR (30) NOT NULL" & _
",PRIMARY KEY (state_name));"

..Execute _
" CREATE TABLE Locations" & _
" (location_id IDENTITY (1,1) NOT NULL UNIQUE" & _
",city_id INTEGER NOT NULL" & _
" REFERENCES Cities (city_id)" & _
",state_id INTEGER NOT NULL" & _
" REFERENCES States (state_id)" & _
",PRIMARY KEY (city_id, state_id));"

End With
End Sub