does referential integrity auto add to child table?

[Dylan Snyder]

NEw to access. I have a parent child relationship. I thought that referential integrity automatically added new records to the child table. Am I wrong? Is there a workaround solution to add new records?

 

[ajetrumpet]

NEw to access. I have a parent child relationship. I thought that referential integrity automatically added new records to the child table. Am I wrong?

Yes, you are. That's the purpose of the integrity enforcement. By enforcing this, it enhances the certainty that you have specified you want between the tables and guarantees you the "relational" nature that the program is built for.

Is there a workaround solution to add new records?

Add new records to a child table when there is no matching record in the parent, and integrity has been enforced? NO, there isn't.

There isn't much difference though between a one-to-many with enforcement and a one-to-many without enforcement. You will have to do without if you want to add your child records before the parent record is added.

If you have users though, the biggest issue will probably be data validity, so watch out for that.

 

[Dylan Snyder]

thanks, what I really want to do is add records to the Child when a record is added to the parent. Is this possible or would i need to add the record on the form for the child table and have it add the appropriate data from the parent?

 

[ajetrumpet]

Dylan,

The key to the integrity check is the joined field. With the enforcement, the only value that has to be present in the parent table (to actually inform the program that there is a parent record in existence) is the joined field. Once you have that in the parent table, add as many child records as you want.

If you're trying to add these records (parent and childs) at the same time (via code, or something like that), you can do it all in one step, as long as the parent information is first in sequence.

 

[Dylan Snyder]

Thanks, got it now

 

[ST4RCUTTER]

I am attempting something similar due to having to deal with mutiple tables with a one-to-one relationship with a main table.

I have added the subforms to the main form for each table and established the parent-to-child relationships for each table. Each child table has an autonumber PK. When I create a record on the main form I can see the FK showing the matching number on the subforms, but when I close the mainform, only the main table has the record. None of the child tables are adding the record.

I wonder if what I am attempting is even possible without VBA code...

 

[Dylan Snyder]

thanks, There needs to be a "Cascade addition" button that adds new records from the parent to the child. It makes complete sense, but the answers I'm getting from the forum is that it doesn't automatically enter it. I'm finding a workaround whereby the form for the child table enters the new record-but it would be so much easier if access would just add the record. Oh well, I guess this is why they say not to do one to one tables...(I don't have a choice, though)

 

[Mike375]

When I am adding a new record to the Many table I open a form based on that Many table and have it set to "new record" and ID number inserted from the form based on the One table.

With referential intergity enforced and cascade Update related fields nothing happens in the Many table (s) if I add a record to the primary table. However. if tried to add a record to the seconday table I could only do so if I used an ID number that existed in the primary table.

If I changed the ID number in the primary table then the matching records in the secondary tables would all change the ID number.

 

[Pat Hartman]

Sorry Dylan but "Cascade addition" doesn't make sense which is why there is no such feature. How would Access or any other RDBMS khow what the child records should contain or how many records it should create.

In 1-1 relationships, only ONE table has an autonumber as its primary key. That table should be the "parent" in the relationship. The related "child" tables MUST have primary keys that are long integers.

When working with 1-1 relationships, there is no need to use subforms. In fact, using subforms causes more work.

With a form/subform, the "parent" is added first using the main for. The "child" is not added until at least one field of the subform is populated.

With a single form based on a query that joins the parent and child tables, you still need to populate at least one field of the child record in order for it to be added.

Relational databases allow 1-1 and 1-many relationships to be sparse. That means that just as in real life, the 1-side may exist without a child-side. If your business rule is that the "child" is required, it is up to you to ensure that the child record gets added, even if you have to do it behind the scenes.

Referential integrity is intended to prevent orphans, not to create children.

 

[ajetrumpet]

Referential integrity is intended to prevent orphans, not to create children.

Hey, I like that line!

 

[Mike375]

And me too!

 

[ajetrumpet]

Each child table has an autonumber PK. When I create a record on the main form I can see the FK showing the matching number on the subforms, but when I close the mainform, only the main table has the record.

Backing up Pat on this one. Autonumbers certainly don't belong in child tables. For another way of thinking, autonumbers are "initiators". As in, they make the "first move". Thus, they are the "bosses" of the table. Child tables are objects that are "dependent" on something else (their parent). Thus, they are not in charge.

 

[Dylan Snyder]

Thanks guys-Here is how it works in my db. A new item comes in and the item information is enetered. Then, days later, the duty rate info on the item is posted. The Duty rate info is located in the child table and entered through a different form. The tables are related in a one to one relationship with the Primary key autonumber from the parent being the long integer primary key in the child. When the item info is entered in the parent, I would like the long integer primary key to be entered as a new record in the child so that it will automatically be an updateable record on the duty info form. So you're telling me this I can't do with out form/code magic?

 

[neileg]

Seems to me that you've got this wrong. Why have a separate table? When you first create the record you wouldn't show the fields relating to duty on the form, but they would still be there. Then when you add the duty details you show whatever existing data you need plus the fields relating to duty. No need for a separate table at all. Or am I missing something?

Are you thinking that a form has to show all the fields from a table?

 

[Dylan Snyder]

I completely agree with you except The tables need to be in different databases. Er, really they don't-I could just link to the table in the duty rate database. But, bottom line is that my boss says it has to be done. So, I have to break it apart and completely redo the forms so that it works seamlessly. I would much rather be working on some other project, but I don't have a choice. And obviously, I'm no access pro so this may take some work over this weekend...arggh

 

[neileg]

None of that tells me that you need to have two tables. If you always have one parent and always have one child then you can have all of the data in one table. For what you are doing I can't see the point.

If your boss understands database design, let him tell you how to solve your problem. If he doesn't, he got to be told that there is a better way.

You may have to upgrade to BOSS 3.1 Sp2 because this service pack greatly reduces the interference weighting.

 

[Dylan Snyder]

Thanks. You're right. There must be a part of the equation that I'm not understanding from my boss, because everything I'm hearing is that one to ones are not essential. And if there isn't, I can work on a more beneficial project. If you don't mind, I have another rookie question. If I were to bring two tables together through a query and make an updateable(the user can alter and add data) form from that query without using VBA? That would save me a lot of time so I wouldn't have to keep making form/subform combos for each of these. Again, I have a very basic understanding of access and this multiple table update process has set me back a bit.

 

[neileg]

If I were to bring two tables together through a query and make an updateable(the user can alter and add data) form from that query without using VBA?

A piece of cake. Build your query and let the form wizard loose on the query.

 

[Pat Hartman]

The reason you use form/subform combinations for 1-many relationships is because you don't want to repeat the 1-side information in all the many-side records. For example, if you were to create a query that joined OrderHeader with OrderDetail and created a single form to manage the data, there would be no easy way to move from one order to another without scrolling through each line item. Plus on the form, every Detail record would contain what looked like a duplicate of all the header fields.

Whether somethig is more or less work is irrelevant if the "less work" solution doesn't make any logical sense.