Linked Table Relationship Problem

[ZoeAbby]

There has to be an easy answer to my problem, but I give up. I'm pretty much a novice trying to modify what seems to be a fairly complicated database. Here goes.

The format for all of the database tables are stored in a master database and linked to the database (user database) with all of the forms, queries, etc. I am trying to add fields to one of the tables. I added the fields in the master database, made a new linked table in the user database, and created the links in the master database. My problem seems to occur with the relationships for the user database. When I create the relationships, the check boxes to enforce referential integrity, etc are shaded and cannot be checked. I am working from a copy of the database so that I can compare to the original that works. In the original database, all three boxes (enforce referential integrity, etc) are check in the master database and in the user database only enforce referential integrity is checked. In the user database, the relationship type is "One to Many - External." It SEEMS that my problem is that I can't check the "Enforce Referential Integrity" box for my relationship. Also, I don't know how to get "One to Many-External" to appear. Mine just shows "One to Many."

The error caused by my problem is as follows:

I have a subform where a combo box is used to populate a field. The source items for the combo box appear properly, but when I try to select an item input after I modified the linked table, I get the following error "You cannot add or change a record because a related record is required in Table xx." Everything I can find indicates this is a relationship problem. Interestingly enough, if I select an item that I input prior to change the table, it is accepted and input without error.

I know this is long, but I don't know how else to explain the problem.
Thanks

 

[Pat Hartman]

I am confused by your description but I'll take a stab at what may be the problem. Referential integrity can only be enforced between tables in the same database. So, if you have a table in dbA and another in dbB, you can create a relationship between them but it is basically for documentation. It doesn't do anything since you can't enforce RI. If that isn't the problem then perhaps it is that you havent specified primary keys for the tables you are attempting to relate.

The relationship will be described as One to Many (external) when the relationship exists in the source db. So if tblA is related to tblB in their source db. If you link to tblA and tblB from a second db, the relationship will be described as "One to Many (external)". Whereas when the relationship is between two local tables or one local and one linked, it will show as simply One to Many.

 

[ZoeAbby]

Sorry for the confusion. It is somewhat difficult to explain. My problem is that I can't write new data to my table from a form. I get the "You cannot add or change or record because a related record is required in the table." I have a database the completely works. I needed to add a couple of fields to one of the tables, which I thought would be simple enough. After doing this, my relationships don't show up the same for my modified database as they do for the original, working database. The primary keys are all set the same as they are in the working database.

My scenario is as you describe it for the external One to Many relationship. Relationships are established for Table A and Table B in the sourcedb. I am linked to tbA and tbB from a second database. Could the order in which I'm doing things be causing the problem?

I am adding fields in the source DB, re-establishing the relationships, then creating a new linked table in the 2nd DB. When I create the new linked table in the 2nd DB, there are no relationships showing for that table. I can't find much on the subject, but it seems that the relationships should transfer with the link. But, they don't. So, I have tried countless times to create relationships for the newly linked table. I can establish relationships, but not Referential Integrity. I'm just guessing that is my problem, because my working DB shows RI for the tables in the 2nd DB. Is it possible that the relationships need to be established between tbA in the source DB and tbA in the 2nd DB and tbB in the source DB and tbB in the 2nd DB?

It is difficult in that someone else created this monster. I'm not clear as to why 2 databases are used. I'm guessing size.

Anyway, any other suggestions would be greatly appreciated. I don't give up easily...

 

[Pat Hartman]

If tblA and tblB are both in the back end db, that is where the relationships must be defined. RI will be allowed and should be enforced. Relational databases, Jet is no exception, do not generally allow you to establish relationships between tables in two different databases. When you link to tables in the be from the fe, there is no need to define relationships. They already exist and will be enforced. Nothing you do in the fe will change that. You must have noticed that if you add tblA and tblB to the relationships window in the fe db, the existing relationship automatically shows up.

Unless you changed the pk or foreign key, when you made the table changes, relationships should not have been altered. So, that isn't your problem. More likely is that something changed the master/child links in your form and so the foreign key is not being automatically populated in the subform.

 

[ZoeAbby]

Thanks, I think I 'm getting somewhere now. The relationships for TbA and TbB are both defined properly in the be database (meaning to me they look exactly the same as they do in my working database). However, when I am linking to TbA and TbB in my fe database, I'm creating a new table using the table wizard and clicking on linked table. Then I just select the table I want. No relationships show up in my fe databse. I've been creating them in the fe database. That has to be my problem in why the relationships aren't correct. Now, my question is what am I doing wrong in linking my fe database tables to the be database? If I can get these relationships right, I think it will all work. Thanks again for all of your help!!

 

[ZoeAbby]

Thank you all for your help! I've finally had success. I'm one of these people who just can't stand the thought of being defeated by a computer program.

Anyway, I just had a thought.. I deleted all of my linked tables and relinked them. Everything now works. I had thought that since I just modified one table, only one new table needed to be created. Apparently not. And how much time did I waste? Hopefully no one notices.


Thank you all again! I'm not finished with this yet and am sure I will be back.

 

[FlyingDisc]

Hello,
I am currently experiencing a problem that seems to be identical to yours: the tables have a one to many relationship set up correctly in the backend, but the linked tables in the front end does not display the relationship (no little (+) to drop down related records). Can you please go into more detail as to how you solved the problem?

 

[boblarson]

Hello,
I am currently experiencing a problem that seems to be identical to yours: the tables have a one to many relationship set up correctly in the backend, but the linked tables in the front end does not display the relationship (no little (+) to drop down related records). Can you please go into more detail as to how you solved the problem?

1. You should not care about the (+) anyway. You shouldn't be working in tables directly.

2. You create forms and subforms to work with the table data.