Re: A philosophical newbie issue: catch redundant errors via relationships or programmically?

From: David Cressey <>
Date: Mon, 31 Dec 2007 19:31:36 GMT
Message-ID: <ssbej.874$nN5.815_at_trndny04>

"Tony Toews [MVP]" <> wrote in message
> "David Cressey" <> wrote:
> >Thanks. I could not get the compound key to work in Access, which is
> >a bit strange (the restriction on whether a foreign key is a duplicate
> >or is unique is rather hidden). After many permutations, I gave up,
> >but it could be a peculiarity of Access.
> >
> >I could be wrong, but I don't think Access has any particular limitations
> >this regard.
> I did an Access database using compound keys back in A2.0 in about 1995 or
so. If
> anything it works a bit better these days.
> Tony


This is the second time around with Ray and compound keys, here in comp.databases.theory.

A little while ago some of us walked him through setting up a compound key for a junction table in MS Access. (In spite of the fact that none of us work much with Access). That worked, according to Ray.

Ray may not have recognized this as another instance of exactly the same problem.

Interestingly enough, if you ask Access 97 for help with relationships, and select the topic "creating a many-to-many relationship" it tells you to make a junction table with a compound key. This is exactly the advice I gave, with more detail on how to do it.

But if you turn to some of the books about working with access, or if you take you cue from the "Northwind" database, you'll get advice to create an ID field for the junction table, and declare that as the primary key. Unfortunate for the newbies.

I do not know if all this has been improved in later versions of Access, nor whether I would agree with the Access community about which direction "improvement" lies in. Received on Mon Dec 31 2007 - 20:31:36 CET

Original text of this message