Missing Tables/Relationships - Explanation/Solution

[TheVampyre]

Hi All,
Ive imported an XML into Access, which was fine, it has brought all of the tables that i expected to come through.



However, when I go to Database Tools>Relationships, there are two of the tables missing from the diagram and their relationships. In the tables where it has created PK/FK these keys are blank.


But if i run an report, it knows thsat these are supposed to be there.



BTW i have already checked the XML for errors by re-importing back into another programme and no issues with the relationships are shown.


Does anyone know why these are missing? Also how do i fix it/stop it happening again ?



Many thanks



D

 

[isladogs]

1. Have you tried adding the tables manually to the Relationships window?
2. Some import methods import the field but not the PK property. I don't know whether that happens with XML import as I've not done that for a long time.
Have you checked if the PK property exists in table design view

 

[TheVampyre]

Hi @Ridders


Thanks for coming back to me.



Yeah I can always add the tables and relationship manually, i just wanted to know what happened really out of inqueisitiveness.



Yeah the PK does exist.



I did read something about issues with render, is that a thing you are aware of?


Its just really stranges as out of about 30 tables with various relationships, just two are missing and they arent connected, looks completely random. Which is again frustrating as they dont seem to have a commonality.



Many thanks in advance

D

 

[isladogs]

Sorry no ideas beyond what I've already mentioned

However a quick Google search for 'relationships window render' did lead to this link which may be useful: https://docs.microsoft.com/en-us/windows/desktop/winmsg/window-features

 

[TheVampyre]

Thanks @Ridders, for looking.



Anyone else have experience of this?



Many thanks D

 

[TheVampyre]

Hi All,


After doing some fishing about and testing, i have found the reason and one that may be of use to the rest of you.

The issue with the missing relationships was caused by the two table names added together, breaching the character limit for a table name. The maximum length as you will know, for an object/table is 64characters. So the two table names that have a relationship are say 30 chacters and 40 characters, the relationship will not be shown in the relationship view.

The tables themseleves will still appear within the database, no issue there. But the relationship view will not show the link.

You can of course manually add the relationship between them by dropping the table onto the diagram and editing the relationship, there are no prompts (thanks Microsoft).



Just thought it would be good to share the information.



Many thanks


D

 

[isladogs]

Congratulations for working that out.

There are many good reasons for using short names for objects. E.g. reduced typing, easier to read etc.
You've just discovered another one.

Why on earth would you ever have table names of 30-40 characters?

 

[TheVampyre]

Thanks @Isladogs.


Well in all ways I indeed agree with you and anything like this brevity is always something to strive for.

However in this example, which I will give you a brief run down of:


We create data schemas for clinical use, these schemas obviously to moderate and/or validate the date that flows through clinical departments. In this occasion, we are creating such a schema, but the user also needs an access database based on the schema, so users can add data from there own queries into the database before submitting. These items that have become tables with attributes, have lengthy clinical names based on a central data dictionary for referencing said data within the larger organisation.



I hope that gives you an idea.



Many thanks


D

 

[The_Doc_Man]

These items that have become tables with attributes, have lengthy clinical names based on a central data dictionary for referencing said data within the larger organisation.

That concept is another good reason to find some way to reduce the name. Generated table names derived from data will surely have no rational limits other than the size of the data field, which leaves you at the mercy of that central dictionary. But if you make that lengthy designation a FIELD within the particular table, you can still find it. And if you somehow generate the table name based on a subset of those longer names, you might still be easily able to manage them programmatically.

Another issue here is whether there is denormalization involved. If you are generating these things from system-created data, the odds are that the resulting tables will frequently or even always have the same structure.

If so, what you SHOULD be doing is creating a temporary table for which the name is almost immaterial. Then, after suitable massaging while in isolation, dump that temp table into a master table along with a designator that identifes its precise original. Perhaps the long name would go into a table that lists the long name and an autonumbered or otherwise synthesized short prime key. Then append the temp table to the master table with that PK and voila' your data gets stored in a table with predictable short field names.