What happens when there are no relationships?

[C Tate]

I am working with a large database. Frankly I have never seen the like of it
before. There are no one-to-one or one to many relationships set as far as I
can see (and no referential integrity set) - though there are thin lines
linking the tables in the relationships window. I cannot understand how this
database can work. If a client is deleted does this not just mean that any
related records in other tables will stay put?!

I would be grateful if anybody can advise on the other sorts of problems
this scenario would pose.

 

[Damien McBain]

I am working with a large database. Frankly I have never seen the
like of it before. There are no one-to-one or one to many
relationships set as far as I can see (and no referential integrity
set) - though there are thin lines linking the tables in the
relationships window. I cannot understand how this database can work.

I've seen guys doing this, they recreate all relationships in their queries.
Not my cuppa tea.

 

[Van T. Dinh]

Some developers prefer to work with no relationships or R.I. In Queries,
they can create links between Tables anyway. For R.I., they prefer to use
code to enforce R.I.

Personally, I use rleationships & R.I. but NEVER use Cascade Delete. IMHO,
it is just to dangerous and to easy to delete related Records by accident.
If I need to delete Parent Record and related Child Records, I delete (by
code if required) related Child Records then delete the Parent Record.

 

[C Tate]

Hmmm ... that is interesting. All the courses I've been on have taught me to
use these relationships. Now I hear they're not necessary anyway!! What I
cannot really understand is how I can ensure that meaningless data is left
sitting about in numerous tables when the main record has been deleted. I
don't know how to do this. What is the best way to start? I can't code I'm
afraid.

 

[Van T. Dinh]

Personally, I use relationships & enforce R.I. and Cascade
Update but not Cascade Delete.

Like I wrote, if your users use Forms to enter / edit data
(and in properly-developed Access databases, they always
do), you can write code to ensure related Child Records
are deleted before deleting the Parent Record by executing
a simple Delete Queries / SQL Strings. In effect, this
ensure that there are no orphaned Records that I need to
delete.

If you already have orphaned Child Records, use a Query to
find them and delete them. The SQL should be something
like:

SELECT * FROM tblMany
LEFT JOIN tblOne ON tblMany.frg_OneID = tblOne.OneID
WHERE tblOne.OneID Is Null

Get a big book on Access if you are serious on learning
Access.

HTH
Van T. Dinh
MVP (Access)

 

[John Vinson]

What I
cannot really understand is how I can ensure that meaningless data is left
sitting about in numerous tables when the main record has been deleted. I
don't know how to do this. What is the best way to start? I can't code I'm
afraid.

The alternative to using referential integrity involves code...
usually quite a LOT of non-trivial code.

Even when I use code to ensure RI (largely to provide more helpful
error messages) I still enforce RI at the table level. Let the program
do the work rather than reinventing the wheel, is my philosophy!