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

[Quoted] Anybody have this problem when using an old form on a new database Relationship? I'm pretty sure it's a common problem (hint: don't do it--always generate a new form if you change the primary keys in a database schema / architecture).

On Dec 31, 11:31 am, "David Cressey" <cresse..._at_verizon.net> wrote:
> "Tony Toews [MVP]" <tto..._at_telusplanet.net> wrote in messagenews:1pfin3d2ll6t12ntvc659ek9ekt9bv1prj_at_4ax.com...

> > >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
> inthis 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
>
> 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.
>

[Quoted] OK, you'll like this. As fireworks go off outside (I'm ahead of your time zone), and no wild parties to go to, with a social event looming tommorrow, very little sleep (this programming stuff is addictive you know), I spent a good part of two hours trying to get this dang new schema to work.

[Quoted] I finally figured it out, and it took me a while: Access does indeed allow compound primary keys, relationships between compound keys, and the like between tables. No problem whatsoever (I generated from [Quoted] scratch just such a form, so I know it can be done). However, in my particular case the problem is this: if you take an old *FORM* (that is, the Access' front end data entry GUI), that has been customized for use with artificial non-compound keys (i.e. GUIDs, Long Ints, etc), apparently, and I'm almost certain of this, the FORMS (not the tables) contain meta-data on 'indices' and the like, that prevent you from using the old form with the new compound keys. As proof of this, I finally saw that the form, when used to create a table having a subform (child table), was not generating a foreign compound key (one of the two), and the column for this key was missing. I tried and correctly specified the "master-child' link for the subform, the proper generic SQL query for both parent and child subform, and the like, and still when it came time to enter more than one record, I notice when I clicked on the raw table a certain compound key "Stock_ID" for the subform was not being generated (the column was missing, literally). When I manually inserted it, and fired up the form again, I got a warming that there was ambiguity in the name of the stock_id column (since in Access there's an annoying but convenient habit of not enforcing name distinctions much--you often have the same name floating around with little if any scope resolution operator, but I digress). The point of the exercise was to show that the old form is messed up. I'll cross post this in microsoft.public.­ access.­formscoding to see if it rings a bell.

I am very confident that I could get the compound primary keys (being used as foreign keys in a subform table) to work, IF I USED A NEW FORM. That is the key--no pun intended--using a new form when you change the keys radically, and you're in form.

ANybody else have this problem? I'm 99% sure this is Access specific and not in anyway a bug of Access, but a really nasty little undocumented feature!

> 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.

BTW, even with the GUID keys in my original schema, I can get the dB to work fine, except, like I've said, I have to programically check for duplicate entries--not the end of the world for a small database like mine.

[Quoted] Happy New Year! I'm going to bed...

RL Received on Tue Jan 01 2008 - 00:31:12 CET