Sub-Form relationships missing - I'm out of my depth

[GeoffT]

I have been asked to make some changes to an existing database.

This database has worked well for five years.

Within the central form (containing records with people details) there are four existing sub forms, the tables under which each contain only an ID field and a date field.

I am going to add another sub-form and as a start I had a look at what is already there.
To my surprise when I went to look at the table relationships I find that no relationships have been defined. According to my reading it is essential to define a relationship between the undelying central table and each table underlying a sub-form.

Furthermore, I see that none of the underlying tables for the sub-forms have a primary key defined.

I do understand the basic priciples of relationships.

I'm out of my depth here and would appreciate any advice as to what might be going on before I charge in with my own changes.

Regards.
GeoffT

 

[dbDamo]

It sounds to me like you are looking for the table relationships in the Front End Database, where all the forms, queries, macros etc are stored. The table relationships should be set in the Back End Database where the actual tables are stored, so I would suggest you look there.

If my assumptions are not correct and your Database either isn't split, or it is and there are no relationships set in the Back End then it would be helpful if you could upload the Database.

 

[Galaxiom]

It is not essential to set any relationships in a database unless you are using referential integrity. Otherwise relationships only really matter in queries and in this case they are essentially defined by the joins in the query.

Despite the often repeated dogma, it is not essential to have a key field in every table. They do need a key if a specific record has other records joined to it. Loosely speaking, the lowest level of tables in the tree don't usually require keys.

Keys are also required if the recordsource of a form is a query so that a specific record can be identified when values are updated.

However there are good reasons not to have a primary key in many tables. Whenever a value in a key field is edited or a record added or deleted the field's index must be updated as well as the table. This is a waste of processing time where a key is not required. Similarly, indexes should be applied sensibly. Don't just stick one on every field in the belief that it will speed things up. It won't.

Some developers even go so far as to eschew any form of natural key, especially composite keys and claim that every table should include an autonumbered primary key. This is simply not true.

 

[GeoffT]

For dbDamo
And Galaxiom

Many thanks for your replies. I have to be off-line for a few days now but will be doing a bit of reading whilst away.

dbDamo

I'm a bit lost as to whre I should be looking for relationships Front End / Back End . I'm using Access 2007 and can find only one route to relationships - under the DatabaseTools menu then Relationships.​

Galaxiom​

I'm going to spent a bit of reading time on brushing up on referential integrity over the week-end!
​

Many thanks to you both.
Will get back to you next week.

GeoffT

 

[Galaxiom]

Sounds like maybe the database is not split? If it is split the tables in the Navigation pane will show a small arrow and when you hover they will show their source database. The Linked Table Manager button in the Database tools ribbon will also be enabled.

If the database is not split that needs to be sorted especially if you have multiple users.