Referentail Inegrity

[Lou_Reed]

I am working on a database and have run across this problem. Whenever, I create a link between two tables there is no issue (it evens tells in the editing links dialogue box that it is a one-to-many relationship). It seems to follow all of the rules I have learned in creating a Microsoft Access 2010 database.

However, when I try to edit the relationship link (which already tells me the type of relationship) and check the box to enforce referential integrity I get a long
message about how that cannot be done. It says that I must have a record in the table with the foreign key that has no counterpart with the table that has the primary key.

That is simply not true, but MS Access 2010 says it anyway. I know it is not true because the database is empty of any records. Thus how can this relationship flaw be accurate?



I am confused, but I do not think about referential integrity since I have done that repair that already in my database.

Any help appreciated. Thanks in advance.


Respectfully,


Lou Reed

[RuralGuy]

I believe you have to supply at least one record in the table. Access cannot compare Nulls.

[Lou_Reed]

Okay, but this is not the only relationship that I have in the database. It is the first relationship and a very important one, but it is not the only one. They also have no records, and why are they not complaining?

I agree that MS Access 2010 can not compare nulls, but why is this the only one to complain?


Thanks in advance.

R,


Lou Reed

[RuralGuy]

I agree. Have you tried a Compact and Repair yet?

[Lou_Reed]

I will try compact and repair and discuss what the result is. By the way, I do not see the purpose of compact and repair, please explain.

R,

Lou Reed

[Lou_Reed]

I tried compact and repair and there was no change in the database at all. I could not edit the relationship and attach referential integrity.

I think it might be a good idea for me to post the database since I am out of ideas to try.

R,

Lou Reed

[Lou_Reed]

Here is my compressed database file that does not allow any type of referential integrity.

I am not sure why it complains whenever, I check the box for referential integrity. As I sad before the db is empty.
Any valid explanation would be appreciated. Thanks in advance.

Respectfully,


Lou Reed

[RuralGuy]

C & R does a lot more than is obvious. It was worth a try. Thanks for the db.

[RuralGuy]

If your issue was with the tblOrg and tblPersonnel, tblPersonnel had one record in it and the OrgID was not in the tblOrg table. I just deleted that one record and it now enforces RI.

[orange]

Lou,
Further to Allan's suggestion, I did a review of your tables and found the following record counts.

Accesstbl  0
MediaBits  0
tblCSWF  0
tblCtr  0
tblLocation  0
tblOrg  0
tblParking  0
tblPersonnel  1
tblProject  0
tblProjectPersonnel  1
tblPropPass  0
tblRoleList  0
tblSafes  0
tblSecurity  0
tblSked  0
tblTelework  0
tbProjectRole  0

I don't know how relevant it is, but it doesn't support your

As I sad before the db is empty.

Anyway good luck with your project.

[RuralGuy]

Lou:
I do not see a reason for the tblRollList table. It is redundant as the RollName is already in the tbProjectRoll table.

[Lou_Reed]

Thanks for your help. I hasten to add that this is a beginning db. It is very raw. I am not sure how records are in the db, there should not be. I certainly did not put them in.

VR,

Lou Reed

[RuralGuy]

I believe you are good to go on this thread. If so then use the Thread Tools at the top on the thread to mark this as Solved!

[Lou_Reed]

I will use the thread tools as you instructed. I have one last question.

If there was a record in tblProjectPersonnel (there was, I checked) then how come I did not get the same problem that I had with
tblOrg and tblPersonnel. It should have complained when I checked the box for referential integrity in the edit relationships box, but it did not.

Just answer this question and I will mark the thread as solved.

Respectfully,


Lou Reed

[RuralGuy]

It could be because you linked ProjectID to PersonnelID.