manual deletion of records in datasheet based on joined tables

[glenndm]

Hi,
Can someboy please explain how access decide what to delete when manual
record deleting on joined tables?

the joined table is shown in a datasheet where the user deletes a record
using the record selector and pressing the delete key.

Main table = invoice list containing customer IDkey
linked table = customer list

the query for the datasheet:
select invoices.number, customer.name from invoices left join customer
on invoices.ID = customer.ID

invoices is local table, customer is an external table. as consequence,
relation integrity rules are disabled on the join

goal: delete invoice record, keep customer data

setup 1:
invoice ID field is primary key
customer has no primary key

what happens:
upon record deletion, the record in the customer table is deleted, the
record in invoices is not.

setup 2:
invoice ID field is primary key
customer ID field is primary key

what happens:
upon record deletion, record in invoice table is deleted and record in
the customer table is deleted

---
what must I change to keep the customer and drop the invoice?

the aim is also but a minimal modification of access default operations.

best regards
glenn

 

[Phil Smith]

If you look in the standard query view, looking at a table,
you will see an asterisk, "*", as the first field choice,
followed by all of the fields in the table. Make sure that
you put that asterisk on the query grid, from the table
you want to delete the records from, as it helps Access to
know which table to delete records form.
In this case, make sure you put the asterisk from the Invoice table
in order to delete records from that invoice table.

 

[glenndm]

Hi Phil,

Your tip worked, great!
I just tried it: the invoice is deleted without erasing the customer data.

Thanks very much
glenn

If you look in the standard query view, looking at a table,
you will see an asterisk, "*", as the first field choice,
followed by all of the fields in the table. Make sure that
you put that asterisk on the query grid, from the table
you want to delete the records from, as it helps Access to
know which table to delete records form.
In this case, make sure you put the asterisk from the Invoice table
in order to delete records from that invoice table.

8<

 

[glenndm]

There is a further twist to this. It also depends on whether or not the
link field is a primary key.

query: select invoice.*, customer.name
from invoice left join on customer
on invoice.ID = customer.ID

if neither table.ID is primary, deletion is not allowed
if both table.ID are primary, both records are deleted (invoice and
customer)
if only invoice.ID is primary and not customer.ID, customer record is
deleted.
if only customer.ID is primary and not invoice.ID, invoice record is
deleted.

best regards
glenn

If you look in the standard query view, looking at a table,
you will see an asterisk, "*", as the first field choice,
followed by all of the fields in the table. Make sure that
you put that asterisk on the query grid, from the table
you want to delete the records from, as it helps Access to
know which table to delete records form.
In this case, make sure you put the asterisk from the Invoice table
in order to delete records from that invoice table.

8<

 

[Phil Smith]

Interesting. I am not sure I can help anymore with explanations as to
why, as I generally try to avoid manually deleting things from datasets.
However, in order to get the job done:

You could add a Y/N field to invoices called DeleteMe, then rather then
manually delete, manually edit that field. You could even build a
continous form if others will be regularly doing this task.

Then run an query to delete all invoices where DeleteMe="y".

Since the delete query would not even be linked to your Customer Table,
that table would be safe, and only the invoice records need to worry...

 

[glenndm]

normally, I'd implement my own delete procedures as you also suggest.
However, I find there's a lot of work disabling regular access functions
which could interfere - users are renowned for clicking where they
shouldn't. The work at hand was limited, so I tried to simplify.

I just realize this perhaps is not the case if the runtime is used? Up
till now, I only used full access. something to check out.

thanks for your help
glenn

Interesting. I am not sure I can help anymore with explanations as to
why, as I generally try to avoid manually deleting things from datasets.
However, in order to get the job done:

You could add a Y/N field to invoices called DeleteMe, then rather then
manually delete, manually edit that field. You could even build a
continous form if others will be regularly doing this task.

Then run an query to delete all invoices where DeleteMe="y".

Since the delete query would not even be linked to your Customer Table,
that table would be safe, and only the invoice records need to worry...

8<