Back table not deleting?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan - 04 Jun 2007 16:53 GMT

Hi everyone,

It appears that my back table isn't deleting the main record and the related
sub-records when you delete the main record from the form. I've double
checked the relationships and all are correct with referential integrity
turned on (one to many)with cascade deleting selected. If you go into the
form and delete the record it looks like it's gone and you can no longer see
it but if you go into the backend and open the table, the record is still
there along with the sub-records.

Has anyone seen or know why it's doing this? Access isn't throwing any
errors like something is wrong. It gives me a confirmation window asking me
if I want to really delete the record and if you click yes, the record
disappears.

Thanks,

Alan

Reply to this Message

Alan - 04 Jun 2007 17:05 GMT

Oh yea I forgot to add, if you delete the main record from the backend, it
comes up with a confirmation window indicating that it will be cascade
deleting all related records which after clicking "yes" it does in fact do
properly so I'm not sure why this isn't working directly from the frontend.
The tables are all linked and not imported so my assumption is what you do on
the frontend will be communicated to the backend or at least that's what I
thought.

Alan

> Hi everyone,
>
[quoted text clipped - 14 lines]
>
> Alan

Reply to this Message

Jerry Whittle - 04 Jun 2007 17:14 GMT

How are you deleting the record? Pressing on the Delete key or a button on
the form? If a button, I'd check the code and see what it's doing.

Are you sure that you are checking the right BE? You might be linked to a
copy used for development or a backup. Been there. Do that myself.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Oh yea I forgot to add, if you delete the main record from the backend, it
> comes up with a confirmation window indicating that it will be cascade
[quoted text clipped - 24 lines]
> >
> > Alan

Reply to this Message

Alan - 04 Jun 2007 17:38 GMT

Hi Jerry,

I'm using the default "Delete Record" button from the menu bar so basically
highlighting the record, right-click, "Delete Record".

Alan

> How are you deleting the record? Pressing on the Delete key or a button on
> the form? If a button, I'd check the code and see what it's doing.
[quoted text clipped - 30 lines]
> > >
> > > Alan

Reply to this Message

BruceM - 04 Jun 2007 20:53 GMT

The question remains whether you are deleting from the backend you expect.
You should verify that you are using the correct linked tables.

> Hi Jerry,
>
[quoted text clipped - 53 lines]
>> > >
>> > > Alan

Reply to this Message

Pat Hartman (MVP) - 04 Jun 2007 21:25 GMT

In order for cascade delete to work properly, you need to use a query that
selects the record(s) that you want to delete but NOT any of their children.
Jet deletes records at the lowest level of the hierarchy in the query. For
example, if your query returned data from customer, order, and order details
tables and you deleted a row - only the order detail line would be deleted -
not the order and not the customer - even if that order detail was the only
row for that order and that order was the only order for the customer. The
customer and order would disappear from the query but they would not really
be gone. Only the order detail row would have actually been deleted. If
you want to delete the order, the query must contain only order or only
customer and order. If you want to delete a customer, the query can only
reference the customer table. This is why just opening the table will
delete the row you want and all of the related children but opening a query
that contains children will not delete the parent even if you delete all the
children.

It sounds like your forms are based on queries that select data from
multiple levels of the hierarchy.

> Hi everyone,
>
[quoted text clipped - 17 lines]
>
> Alan

Reply to this Message

Alan - 04 Jun 2007 23:05 GMT

Thanks for the additional replies. I'll have a look through again and see if
I have missed something along the lines of below.

> In order for cascade delete to work properly, you need to use a query that
> selects the record(s) that you want to delete but NOT any of their children.
[quoted text clipped - 36 lines]
> >
> > Alan

Reply to this Message