Informed Delete consent

[Larry Kahm]

I'd like some suggestions as to how to process and present information about
deleting a set of records. The database has cascading delete established
between tblCompanies and tblContacts, as well as between tblContacts and
tblActivities.

While I know that I can simply delete a company record, I'd like to display
the consequences of the action to the person issuing the request. For
example:

Company ABC has:
12 Contacts with
47 associated Activities
Are you sure you want to delete this?

I' thinking I'll be using a form with a large, blank label as a message area
that gets filled in via VBA.

What's the most efficient way of providing these record counts?

Thanks!

Larry

 

[John W. Vinson]

I'd like some suggestions as to how to process and present information about
deleting a set of records. The database has cascading delete established
between tblCompanies and tblContacts, as well as between tblContacts and
tblActivities.

While I know that I can simply delete a company record, I'd like to display
the consequences of the action to the person issuing the request. For
example:

Company ABC has:
12 Contacts with
47 associated Activities
Are you sure you want to delete this?

I' thinking I'll be using a form with a large, blank label as a message area
that gets filled in via VBA.

What's the most efficient way of providing these record counts?

Thanks!

Larry

Create a Query qryHitlist:

SELECT tblCompanies.CompanyName, Count(ContactID) AS CountOfContacts,
Count(ActivityID) AS CountOfActivities
FROM (tblCompanies LEFT JOIN tblContacts ON tblCompanies.CompanyID =
tblContacts.ContactID) LEFT JOIN tblActivities ON tblContacts.ContactID =
tblActivities.ContactID;

Base a Form on this query, and in the form's BeforeDeleteConfirm event, pop
this form up (probably simpler than creating a dynamic label).

 

[aaron.kempf]

with SQL Server, you can enforce much more advanced logic-- using SQL
Triggers.

Sorry-- but Jet has been obsolete for a decade, it's time to move to
SQL Server, if you care enough about your database to
_BUILD_IT_FROM_SCRATCH_ then use a platform that 'just works'.

There's a reason that real software professionals don't use Access.

SQL Server is just as free-- but it just works

-Aaron

 

[Tony Toews [MVP]]

I'd like some suggestions as to how to process and present information about
deleting a set of records. The database has cascading delete established
between tblCompanies and tblContacts, as well as between tblContacts and
tblActivities.

While I know that I can simply delete a company record, I'd like to display
the consequences of the action to the person issuing the request. For
example:

Company ABC has:
12 Contacts with
47 associated Activities
Are you sure you want to delete this?

I' thinking I'll be using a form with a large, blank label as a message area
that gets filled in via VBA.

I would never delete such data. You never know when the user will
want that information in the future. Instead I would create an
inactive flag and use that.

I would also never use cascade delete as if someone ever gets to the
table view then they can do a lot of damage without realizing it.

I have an intense dislike for cascade deletes in Microsoft Access.
And I don't like cascade updates.
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

sorry that you feel like Jet doesn't meet your needs.

perhaps if you knew about the security implications of triggers-- then
maybe you wouldn't be so worried about this.

-Aaron

 

[Larry Kahm]

John,

Sound approach (as always). Thanks for the SQL - that will definitely do it
for this and for other related modules.

I'm still undecided about building just one form and it's CBF for all of the
possible instances, as opposed to multiple query/form sets.

Thanks!

Larry

 

[Larry Kahm]

Thanks Aaron,

But the client has Access installed throughout the organization. And
despite my repeated request to upgrade the back-end to SQL Server, they have
neither the budget nor the IT expertise to justify it. I can only hope...

Larry

with SQL Server, you can enforce much more advanced logic-- using SQL
Triggers.

Sorry-- but Jet has been obsolete for a decade, it's time to move to
SQL Server, if you care enough about your database to
_BUILD_IT_FROM_SCRATCH_ then use a platform that 'just works'.

There's a reason that real software professionals don't use Access.

SQL Server is just as free-- but it just works

-Aaron

 

[Larry Kahm]

Tony,

I appreciate that particular enlightening piece. I have always checked
"cascade update" (since version 1.1) even when all I was using was the
Access-generated autonumber. No harm, no foul - right?

However, I do understand the awful implications of the cascading delete.
Which is why I printed out the relationships page and documented the
potential horrors.

Can I implement a "delete" flag in the record at this point? Probably. How
many queries, forms, and reports would be affected? I'll have to use Access
Analyzer to be certain. But I'd like to know more about how to implement
flagging the record effectively throughout the chain - or is this where the
cascading update comes into play?

Thanks!

Larry

 

[John W. Vinson]

I appreciate that particular enlightening piece. I have always checked
"cascade update" (since version 1.1) even when all I was using was the
Access-generated autonumber. No harm, no foul - right?

And no use, either: since an Autonumber field is ipso facto not editable,
there can never be any updates to cascade. It doesn't do any harm or any good
to have cascade updates set on an Autonumber, because it will never be called
upon!

 

[Tony Toews [MVP]]

I appreciate that particular enlightening piece. I have always checked
"cascade update" (since version 1.1) even when all I was using was the
Access-generated autonumber. No harm, no foul - right?

John already explained why this doesn't matter for autonumbers.

Can I implement a "delete" flag in the record at this point? Probably. How
many queries, forms, and reports would be affected? I'll have to use Access
Analyzer to be certain. But I'd like to know more about how to implement
flagging the record effectively throughout the chain - or is this where the
cascading update comes into play?

Yes, you have to use tools such as Rick's Find and Replace to find all
locations where the tables are used and add the Where clause. Note
that there will likely be a *lot* of places as, for example, the
customer table is used in a lot of locations.

You also need to add some ability in the main inquiry screens to
ignore Inactive customers and yet allow the user to quickly search the
list of inactive customers.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

sorry that you feel like Jet doesn't meet your needs.

perhaps if you knew about the security implications of triggers-- then
maybe you wouldn't be so worried about this.

Please don't bother replying with your irrelevant answers.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

perhaps-- if you knew _JACK_SHIT_ about SQL Server-- then the _ONE_
conversion that you attempted would have gone successfully.

eat a dick, fat lazy stalker asshole

-Aaron

 

[aaron.kempf]

uh, CORRECTION!!!

I can edit Identity fields in SQL Server!

SET IDENTITY_INSERT tblEmployees ON

with Jet-- you just don't have the control that you need.
So sorry that Jet doesn't meet your needs, John.. Maybe you should
move to a database that has the tools to fit the needs of professional
developers.

-Aaron

 

[aaron.kempf]

Access Analyzer?

Are you kidding me?

you _PAY_ for tools that come free with SQL Server?

-Aaron

 

[Michel Walsh]

Correction on the correction.

With Jet, you don't need to 'set' an option to insert a new record by
specifying a value for a field defined as an autonumber. Simply use:

INSERT INTO tableName(fieldAutonumber) VALUES( 1234 )

and no option to set, before.


Any reader can test, all by him/her self, in Northwind. Type, in the
immediate (debug) window :


CurrentDb.Execute "INSERT INTO categories(categoryID, categoryName,
description) VALUES(1234, 'Aaron', 'Doubtful relevancy)"



and on execution, a new record will be appended using the given value rather
than a (auto - matically) computed number.



That has nothing to do with MODIFYING an EXISTING autonumber value, though.


==================
SET IDENTITY_INSERT categories ON

UPDATE categories SET categoryID = 1001 WHERE categoryID = 1
==================

result in an error:

===================
Cannot update identity column 'categoryID'.
===================




Once more, you are irrelevant... and wrong.



Vanderghast, Access MVP



uh, CORRECTION!!!

I can edit Identity fields in SQL Server!

SET IDENTITY_INSERT tblEmployees ON

with Jet-- you just don't have the control that you need.
So sorry that Jet doesn't meet your needs, John.. Maybe you should
move to a database that has the tools to fit the needs of professional
developers.

-Aaron

 

[Larry Kahm]

Aaron,

Even if I used SQL Server as the back-end of this application, I'd still be
using Access on the front-end. The forms designer is easy to use, makes
developing and presenting applications to various clients easy and
straightforward.

That said, I'd still use Access Analyzer to ensure that the code, the forms,
and the reports had no inconsistencies, that I didn't mistakenly call two
different fields by the same name, ensure that all form fields had
associated status bar text, and so on. Plus, it provides complete
documentation for any one who needs to delve into maintenance at any point
in the future.

Tell me: what tool in SQL Server is going to do that for me?

Larry

Access Analyzer?

Are you kidding me?

you _PAY_ for tools that come free with SQL Server?

-Aaron

 

[aaron.kempf]

_YOU_CAN_USE_ACCESS_
but linked tables fucking suck a mother fucking diseased dogs cock

Use Access Data Projects, you won't have to deal with any of this
crap!

-Aaron

 

[aaron.kempf]

uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?

 

[David W. Fenton]

I have an intense dislike for cascade deletes in Microsoft Access.

I agree with you on CASCADE UPDATE -- it only makes sense with
natural keys, and there are very few of those in any of my
applications.

On CASCADE DELETE, I use it sparingly. By default, I have it OFF,
but in certain cases, such as the detail records for an invoice, it
makes sense to have it ON, as there is no reason *not* to delete
those details if the invoice header is being deleted.

The example you give from Northwind is obviously just a bad
implementation -- IT'S SIMPLY WRONG (as are myriad things in the
Access sample databases).

And your complaint about the default error message is *also*
misplace, in my opinion, as any deletion you allow a user to do
should be under the control of your own code. It's only when you
rely on Access default behaviors that you're going to get the
default Access confirmation message, and the fault there is not in
having CASCADE DELETE turned on, but in not coding your own
user-friendly confirmation message.

So, in short, I think your blanket condemnation of CASCADE DELETE is
unfounded. Nearly every app of mine has one or two relationships
with it turned on (because the child records have no reason for
existing independent of their parent). But I have learned over the
years that it should be OFF in most relationships, simply because
not doing so will lose too much data that's crucial to be retained
(you really don't want to delete a customer with invoice records,
for instance, because then your books are completely hosed).

 

[David W. Fenton]

I have always checked
"cascade update" (since version 1.1) even when all I was using was
the Access-generated autonumber. No harm, no foul - right?

You're adding unnecessary overhead, though I'm not sure if it ever
kicks in with an Autonumber.

Why put in place something that is never going to actually do
anything?