Code to check relationships?

[Les Isaacs]

Hi All
We've had a few cases recently where a relationship (with referential integrity) between two tables has been broken by access. I think it happens when a record has been corrupted (I'm addressing this issue!). Is there a way to check with code whether all the required relationships are still intact? I know I could just go to the relationships window and look, but that's more time consuming, and I'm not always here, and it would be better if there could be an alert when a relationship has broken.
Thanks for any help.
Les

 

[CJ_London]

Not quite sure what you mean - if to see which records may be affected, use a query with a left join from the child record to the parent record and set the criteria for the parent record ID to 'is null'. Otherwise known as an 'unmatched' query.

Otherwise look at the MsysRelationships table - but this will tell you what 'is', not what is 'should be'.

You could take a copy of the relationships table as a template, then compare between the two - the szRelationship field is effectively the unique ID

 

[Les Isaacs]

Hi CJ_London
That's great - I didn't realise there's a MsysRelationships table - perfect!
Many thanks
Les

 

[static]

"I think it happens when a record has been corrupted"

That would be my guess.

Corruption is most often caused by data being written incorrectly to the file due to network drop outs or incorrect shut downs. Access opens the file, finds the entry point to add a new record, works out the space required for each piece of data based on its data type. But as the record is being written to the file a small portion of data gets lost or some extra rubbish gets added. Access doesn’t know this and carries on writing, but the file cursor no longer matches the field lengths and data gets partially written across fields.
If all of the fields are text it probably doesn’t matter so much, but if text is written to a number field Access can’t read it and usually the database crashes out.

 

[jdraw]

Sounds like some type of corruption. I'm not sure how relationships could be broken through some regular application code. Sure with some sql or DDL things could be deleted/altered.