Implicit / explicit relationships in Access

[starson79]

Hi I've tried searching the Net for this but not come across any useful answer. I have just taken over several existing databases which I need to tidy-up / upgrade - whatever you can think of. After some investigation I realise that no table relationships have been defined whatsoever. However, all the "relationships" (implicit ones) are "defined" through the VBA code. Why is this? Why have no table relationships explicitly declared? Was it bad design practice? As far as I know - every database I've ever built or worked with has had table relationships declared! Referential integrity and all that!
Thanks for your help.

[NoellaG]

Hi,

maybe the developer was used to work with other database systems like Progress or so where this is common practice. If possible, this is something you should ask the previous developer.

gr
NG

[NTC]

Its not really a problem. This can be hard to explain; but the table relationships that you speak of are a tool to remind & enforce. Also useful for general documentation, and of course needed for automatic cascade deletes feature.

But as long as the proper xreferencing is implemented in data entry/queries/reporting there is no data difference.

Contributing issues on not having fixed table relationships:
* The Name AutoCorrect feature has some reputation for corruption contribution and so the cascade auto delete which is dependent upon it is often not a feature that is used, which in turn reduces the need for fixed table relationships.
* If tables needed to be deleted/replaced - table relationships would have had to be deleted in order to do that - and thus perhaps the relationships were not rebuilt.

I wouldn't get too hung up on the issue overall.

[starson79]

I just wonder why it was designed like this in Access - the tables it uses are massive - because there is no normalisation (I guess if it's joined through queries then the relationships can be defined any old way - so no need to split data into separate tables). Lots of empty cells...
So is the advantage of not specifying table relationships down to speed? And it's easier to change relationships simply by changing the code, rather than deleting and re-specifying table relationships?
This is not a database that is used for a specialised function - it's just a database to hold masses of data for the usual stuff. Customers, orders, products...that type of thing.
Surely if the tables are massive in Access then (well the reason they have me here now) the database gets S-L-O-W! This negates the positive of a database with no table relationships defined.
Uh. Stuck.

thanks for any further help.

[NTC]

whether the tables' cross referencing relationships are defined via the formal table relationships method (feature), or at the query/form/report level I do not believe to have any impact on speed.

if your issue is slow performance, there can be a wide variety of issues involved - each contributing a little. For instance setting up indexing of the most frequently searched fields/column(s) can improve speed - while doing the same thing for too many fields will slow it down.

also delays can be caused at the form level, not the table level - where there are alot of subforms and lookups occuring.

of course the network can be a contributor, as well as the load at the server. So overall one must move thru all the piece parts ...

hope this helps.