New Wine in Old Bottles problem (never use an old form on a new tableschema / architecture)

[raylopez99]

FYI, I trust this is "old news" to Access veterans, but as a newbie it
caught me by surprise.

RL

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

inthis regard.



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.

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.

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

Happy New Year! I'm going to bed...

RL

 

[Baz]

I just did the following:

Create a table "Table1" with an autonumber PK
Create another table "Table2" with an autonumber PK and a long field as a
foreign key to PK in Table1.
Type some data into both tables.
Create a form bound to Table1, and another form bound to Table2.
Add the Table2 form to the Table1 form as a subform, linked on the PK/FK
field.
Works fine as expected.
Delete the relationship between the two tables, and the data
Add two text fields to Table1 and make them the (compound) PK
Add two text fields to Table2 and make them an FK to PK in Table1
Type some data into the tables.
Add the new fields to both forms as textboxes.
Change the master/child relationship so as to link the form/subform on the
two text fields.

Works fine as expected, including being able to add new records to both form
and subform.

You've made a few remarks that I don't understand, such as:

"and the column for this key was missing."
"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"

These comments lead me to suspect that you made a mistake in the record
source of one or both forms. After many, many years of Access development
in umpteen versions, I can assure you that the form stores NO meta data
about keys and indices.



FYI, I trust this is "old news" to Access veterans, but as a newbie it
caught me by surprise.

RL

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


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.

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

Happy New Year! I'm going to bed...

RL

 

[MikeB]

I just did the following:
These comments lead me to suspect that you made a mistake in the record
source of one or both forms.

You are being too kind... He did make a mistake.

Once the binding of the form data controls is removed from the table, there is
no further relationship to the table period.

You can in fact turn all the controls to Unbound controls (including the form)
and programatically change everything to bound controls at runtime, first to
one table and then to the other without consequence (assumption: both tables
are structurally identical).

This should not be debated further.

 

[raylopez99]

The devil is in the details.

Did you use the Wizard, like I did, to set up the Form/Subform? If
so, you might get a different result.

In any event, after two hours of trying to get the form to work, to
the point where I had to manually insert stuff like columns in the
table (without using the Form, since the new columns were not showing
up in the form despite me adding them manually in the form), which
eventually resulted in the Form not just saying that a relationship
would be violated, but in fact getting a "whitened out" subform, I
gave up.

And indeed with a "clean" table, starting from scratch, I was able to
set up a compound key--but I used the Wizard again afresh on the clean
table, not trying to 'manually' change the relationships as set up by
the Wizard (and I'm not talking about adding a control either, after
the Wizard has done it's work).

Bottom line: Visual Basic allows rapid prototyping but the lack of a
rigorous compiler and loose use of namespaces makes it a trap for the
unwary. But if you have years of experience you probably think it's
swell, not unlike a unicyclist thinking a unicycle is easy to drive.

RL

 

[Baz]

Did you use the Wizard, like I did, to set up the Form/Subform? If
so, you might get a different result.

Oh sure, that'll be the problem then...

In any event, after two hours of trying to get the form to work, to
the point where I had to manually insert stuff like columns in the
table (without using the Form, since the new columns were not showing
up in the form despite me adding them manually in the form), which
eventually resulted in the Form not just saying that a relationship
would be violated, but in fact getting a "whitened out" subform, I
gave up.

A "whitened-out" subform is a sure sign that you got it's RecordSource wrong
(however long you spent trying to get it right...)

And indeed with a "clean" table, starting from scratch, I was able to
set up a compound key--but I used the Wizard again afresh on the clean
table, not trying to 'manually' change the relationships as set up by
the Wizard (and I'm not talking about adding a control either, after
the Wizard has done it's work).

Bottom line: Visual Basic allows rapid prototyping but the lack of a
rigorous compiler and loose use of namespaces makes it a trap for the
unwary. But if you have years of experience you probably think it's
swell, not unlike a unicyclist thinking a unicycle is easy to drive.

The fact that you think Visual Basic (or even VBA) has got anything to do
with creating tables, forms and subforms demonstrates your almost total
ignorance. But it matters not: given that your several days' experience
clearly makes you an expert (in your own estimation), I guess you will not
be needing us any more. DLTDHYITAOTWO.

 

[raylopez99]

The fact that you think Visual Basic (or even VBA) has got anything to do
with creating tables, forms and subforms demonstrates your almost total
ignorance.  But it matters not: given that your several days' experience
clearly makes you an expert (in your own estimation), I guess you will not
be needing us any more.  DLTDHYITAOTWO.

The fact that you are so literal means you are a computer programmer
competing with 16 year olds working for peanuts in Bangladore while I
am your boss, LOL!

Hey, in another week I'll be done with this VB project, and it looks
good, even if the tables are not in strict "Third Normal Form".
Access through VB gets the job done, like a rusty but trusty old
AK-47.

Happy coding Baz!

RL

 

[Baz]

I loved your solution to the incredibly difficult problem of counting some
records and displaying the result. I don't know why I bother in the face of
such genius, I might as well give up now.

The fact that you think Visual Basic (or even VBA) has got anything to do
with creating tables, forms and subforms demonstrates your almost total
ignorance. But it matters not: given that your several days' experience
clearly makes you an expert (in your own estimation), I guess you will not
be needing us any more. DLTDHYITAOTWO.

The fact that you are so literal means you are a computer programmer
competing with 16 year olds working for peanuts in Bangladore while I
am your boss, LOL!

Hey, in another week I'll be done with this VB project, and it looks
good, even if the tables are not in strict "Third Normal Form".
Access through VB gets the job done, like a rusty but trusty old
AK-47.

Happy coding Baz!

RL