I don't want referential integrity but I do want cascade delete

[john williams via AccessMonster.com]

How do I set it up so that there is no referential integrity in a
rtelationship of mine, but the related records in the 'many' table, will
still delete if I delete a record in the 'one' table?

I'm a bit of a newbie so I would be grateful for step-by-step help.

Thanks in advance

 

[Douglas J. Steele]

I don't believe it's possible.

 

[john williams via AccessMonster.com]

Even if I can't tell Access to do it automatically, I'm sure someone can
provide me with a bit of code, so that the related records will be deleted.

Thanks

 

[John Vinson]

Even if I can't tell Access to do it automatically, I'm sure someone can
provide me with a bit of code, so that the related records will be deleted.

Thanks

Seems like a very odd thing to want, but you could put code in the
AfterDeleteConfirm event of the form bound to the "parent" table to
run a Delete query on the "child" table using the (unenforced) linking
field as a criterion.

It cannot be done if the user can delete records from a table
datasheet, since tables have no usable events.

John W. Vinson[MVP]

 

[Jeff Boyce]

John

Why?

As in "what reason do you have for rejecting RI?" Personal belief?
Business requirement? Back-end constraint?

Jeff Boyce
<Access MVP>

 

[john williams via AccessMonster.com]

It's very simple:

The company has many members, which is the 'one' table.

Each of them can make mony ordes. The order table is the 'many' table.

Here's the thing: Sometimes, guests will be allowed to make a purchase. So
if there was integrity, I couldn't put their name in, as it needs to match
an original member.

However, if I delete a member, I do want his orders to be deleted.

Hope this helps. If anyone has any ideas, I would be pleased to see them.

Thanks

 

[Jeff Boyce]

And having a "guest" account/user/member doesn't work?

It seems like you COULD use RI and cascading deletes if you added a "guest"
member to your "one" table.

And when you say delete a member, delete his/her orders, your accountant
doesn't have any heartburn?

Good luck

Jeff Boyce
<Access MVP>

 

[john williams via AccessMonster.com]

I don't want a guest member, because the address of all guests are
different, and I need to send them invoices.

It wouldn't do to create a new member for each guest, because there are
many things in the table, and many macros that run, which are only good for
members.

Therefore, it would be best just to create a new order in the Orders table
with the guests names, but still have it automatically delete orders when
needed to save the user time.

 

[Jeff Boyce]

John

Granted I'm not there, and have no idea of the full set of requirements...

I thought the reason you didn't want to add the guests was because you'd
have to add name/address info. If you are invoicing them, don't you need
name/address info?

Is it that you don't want to have guests "clutter up" your list of "real"
members? If so, another approach would be to add a Yes/No field to flag the
guest members.

Best of luck on your project

Jeff Boyce
<Access MVP>

 

[john williams via AccessMonster.com]

The problem with doing that is that there are many fields in the Members
table, some of which are not feasible for guests.

Another problem is that having a new record in the members table for guests
would use up memory, since guests generally make 'one-off' purchases, and
they will be deleted after they've paid. The user would therefore delete
the purchase record, and not the member record, and would waste space if
the guest was still stored.

 

[Joan Wild]

The problem with doing that is that there are many fields in the Members
table, some of which are not feasible for guests.

Another problem is that having a new record in the members table for
guests
would use up memory, since guests generally make 'one-off' purchases, and
they will be deleted after they've paid. The user would therefore delete
the purchase record, and not the member record, and would waste space if
the guest was still stored.

You cannot do what you want, out of the box. You can do this via code.
Create a delete button on your form that checks for the existance of order
records. If it finds any run a delete query to get rid of them. Then run
another delete query to delete the member from the member table.