I've got apps where you *really* wouldn't want to delete certain items by
accident, but the users just have to have a "Delete" button.
My current strategies:
Plan A:
------------------------------------------------------------------------
1) Make the cmd button black and do not give it an accelerator key.
2) Issue two levels of confirmation (Do you want to delete... Do you REALLY want
to delete).
3) Log the delete in the .INI file
------------------------------------------------------------------------
PlanB:
------------------------------------------------------------------------
1) Do not assign an accelerator key to the cmd button.
2) Issue one confirmation
3) Instead of just deleting, move all Parent/Child records affected
to an "archive" DB - same format as production, just empty.
------------------------------------------------------------------------
I think I've done Plan B once or twice. It's not as simple as it may sound at
first because of things like autonumber fields.
Plan A is my usual approach, but I'm not exactly in love with it.
Anybody got a more creative approach to this?
--
PeteCresswell
17  2020 
"(PeteCresswell)" <x@y.Invalidwrote in
news:t7********************************@4ax.com:
I've got apps where you *really* wouldn't want to delete certain
items by accident, but the users just have to have a "Delete"
button.
My current strategies:
Plan A:
-------------------------------------------------------------------
----- 1) Make the cmd button black and do not give it an
accelerator key.
2) Issue two levels of confirmation (Do you want to delete... Do
you REALLY want to delete).
3) Log the delete in the .INI file
-------------------------------------------------------------------
-----
PlanB:
-------------------------------------------------------------------
----- 1) Do not assign an accelerator key to the cmd button.
2) Issue one confirmation
3) Instead of just deleting, move all Parent/Child records
affected
to an "archive" DB - same format as production, just empty.
-------------------------------------------------------------------
-----
I think I've done Plan B once or twice. It's not as simple as it
may sound at first because of things like autonumber fields.
Plan A is my usual approach, but I'm not exactly in love with it.
Anybody got a more creative approach to this?
Don't delete anything. Just have a DELETED flag in the record, and
have the delete button mark it deleted. Then have your
forms/reports/etc. exclude all records marked deleted.
Restoring a deletion then requires nothing more than clearing the
Deleted flag field. And you won't need to bother with archive tables
and the like for the parent/child tables.
Certain kinds of data should never be deleted. For instance, a
customer with invoices can't ever be deleted as that would mess up
the accounting records. You might want to hide inactive customers,
though, such as ones who've not purchased anything in the last year
or two.
The things you describe in the UI sound horrid to me. If they should
be allowed to do it, then let them do it and don't make them feel
bad about it. If not all the users are qualified to make that
decision, then only give the delete permission to certain qualified
users. And a user who is not allowed to delete should have no delete
button visible (that means hiding it instead of just disabling it),
on the principle that you don't want to suggest a user can do
something they are not allowed to do.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Per David W. Fenton:
>The things you describe in the UI sound horrid to me. If they should
be allowed to do it, then let them do it and don't make them feel
bad about it.
Me too - but Plan A's multiple prompts were first thought of by one of my users
who just *had* to have it exactly that way.... So I stuck with it....
I've tried the IsDeleted thing in one application.
Might do it again... might not. What I found is that the added complexity
propagates through the app to a greater degree than one might think.
e.g. Referential Integrity raises it's head when the user goes to delete a
lookup table item that they *know* isn't being used by anybody. Also some
users doing ad-hoc reporting will not know about it and their reports will be
skewed accordingly.
Things like that and having to make sure every single query looks at IsDeleted
put me off of it - and steered me to the archive strategy.
You get 600-700 reports in an app and sooner or later, I'm going to forget about
IsDeleted and the results are going to pass testing because the situation wasn't
encountered and the reports going to be wrong sometime in the future.
Having said all that, you might be right. Certainly I haven't been able to come
up with anything better. The current app is pretty straightforward and I could
probably confine use of an IsDeleted switch to a single table.
--
PeteCresswell
i'd have to agree with David; even when users tell me that data can be
deleted, during process analysis, i rarely take them at their word and build
in a "cushion" of time where deleted records...aren't. solving the reports
problem is simple enough - never bind a report to a table. create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base for
all other queries.
hth
"(PeteCresswell)" <x@y.Invalidwrote in message
news:gm********************************@4ax.com...
Per David W. Fenton:
The things you describe in the UI sound horrid to me. If they should
be allowed to do it, then let them do it and don't make them feel
bad about it.
Me too - but Plan A's multiple prompts were first thought of by one of my
users
who just *had* to have it exactly that way.... So I stuck with it....
I've tried the IsDeleted thing in one application.
Might do it again... might not. What I found is that the added
complexity
propagates through the app to a greater degree than one might think.
e.g. Referential Integrity raises it's head when the user goes to delete a
lookup table item that they *know* isn't being used by anybody. Also
some
users doing ad-hoc reporting will not know about it and their reports will
be
skewed accordingly.
Things like that and having to make sure every single query looks at
IsDeleted
put me off of it - and steered me to the archive strategy.
You get 600-700 reports in an app and sooner or later, I'm going to forget
about
IsDeleted and the results are going to pass testing because the situation
wasn't
encountered and the reports going to be wrong sometime in the future.
Having said all that, you might be right. Certainly I haven't been able
to come
up with anything better. The current app is pretty straightforward and I
could
probably confine use of an IsDeleted switch to a single table.
--
PeteCresswell
I also use this method, but rarely need to have the only not-deleted
query on anything other than parent tables as the child records can't
get selected if the parent isn't. You can go one step further and use a
Deleted Date field to purge things after they have been flagged as
deleted for long enough that they really should be gone.
--
Bri
tina wrote:
i'd have to agree with David; even when users tell me that data can be
deleted, during process analysis, i rarely take them at their word and build
in a "cushion" of time where deleted records...aren't. solving the reports
problem is simple enough - never bind a report to a table. create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base for
all other queries.
hth
"(PeteCresswell)" <x@y.Invalidwrote in message
news:gm********************************@4ax.com...
>>Per David W. Fenton:
>>>The things you describe in the UI sound horrid to me. If they should
be allowed to do it, then let them do it and don't make them feel
bad about it.
Me too - but Plan A's multiple prompts were first thought of by one of my
users
>>who just *had* to have it exactly that way.... So I stuck with it....
I've tried the IsDeleted thing in one application.
Might do it again... might not. What I found is that the added
complexity
>>propagates through the app to a greater degree than one might think.
e.g. Referential Integrity raises it's head when the user goes to delete a
lookup table item that they *know* isn't being used by anybody. Also
some
>>users doing ad-hoc reporting will not know about it and their reports will
be
>>skewed accordingly.
Things like that and having to make sure every single query looks at
IsDeleted
>>put me off of it - and steered me to the archive strategy.
You get 600-700 reports in an app and sooner or later, I'm going to forget
about
>>IsDeleted and the results are going to pass testing because the situation
wasn't
>>encountered and the reports going to be wrong sometime in the future.
Having said all that, you might be right. Certainly I haven't been able
to come
>>up with anything better. The current app is pretty straightforward and I
could
>>probably confine use of an IsDeleted switch to a single table.
--
PeteCresswell
David W. Fenton wrote:
>>Anybody got a more creative approach to this?
Don't delete anything. Just have a DELETED flag in the record, and
have the delete button mark it deleted. Then have your
forms/reports/etc. exclude all records marked deleted.
That's what I was going to suggest, sort of based on the way dBase used
to do things when I was developing in dBase III+ and IV in the late
80s/early 90s. Of course, you could still .pack with that stuff....
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Per tina:
create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base for
all other queries.
I think you guys are winning me over.
The base query idea was the tipping point. I could use a Find-And-Replace
utility to just change all affected table names to the base query name.
Main remaining problems that I can think of are:
------------------------------------------------------------------------------
- The Admin screens where a user might try to add a lookup table row whose
unique name is already there in one or more "deleted" rows.
- Some sort of pseudo RI issue where a user "Deletes" a lookup table row that is
used by one or more non-deleted other table rows - causing various queries to
return Null for the looked-up value.
My kneejerk is to just not implement the flag system for lookup tables and live
with it if a user trys deleting a row that's used by a "Deleted..." record
somewhere.
------------------------------------------------------------------------------
I'm thinking a field named "DeleletedAt" - which would be Null or contain a
timestamp and "DeletedBy", which would be Null or contain a LAN UserID.
I'd also think that the flag sb used only at the top of hierarchical
relationships. No sense "Deleting" child records if the parent is flagged
as deleted because the user will never see same.
I would hope that there's no perceptible diff between IsNull(DeletedAt)
and IsDeleted=False. Anybody know? - although I guess I should set up a
test table with a few hundred thousand recs and try it either way....
--
PeteCresswell
On Sat, 02 Sep 2006 12:19:11 -0400, "(PeteCresswell)" <x@y.Invalid>
wrote:
>Per tina:
>create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base for
all other queries.
I think you guys are winning me over.
The base query idea was the tipping point. I could use a Find-And-Replace
utility to just change all affected table names to the base query name.
Main remaining problems that I can think of are:
------------------------------------------------------------------------------
- The Admin screens where a user might try to add a lookup table row whose
unique name is already there in one or more "deleted" rows.
- Some sort of pseudo RI issue where a user "Deletes" a lookup table row that is
used by one or more non-deleted other table rows - causing various queries to
return Null for the looked-up value.
My kneejerk is to just not implement the flag system for lookup tables and live
with it if a user trys deleting a row that's used by a "Deleted..." record
somewhere.
------------------------------------------------------------------------------
I'm thinking a field named "DeleletedAt" - which would be Null or contain a
timestamp and "DeletedBy", which would be Null or contain a LAN UserID.
I'd also think that the flag sb used only at the top of hierarchical
relationships. No sense "Deleting" child records if the parent is flagged
as deleted because the user will never see same.
I would hope that there's no perceptible diff between IsNull(DeletedAt)
and IsDeleted=False. Anybody know? - although I guess I should set up a
test table with a few hundred thousand recs and try it either way....
I've personally found a tremendous amount of value in tracking user
IDs and timestamps for deletes and modifies. It's not so much as
having an answer when the finger pointing starts, though there can be
great value in those situations, as the knowledge that the information
is stored makes users much more cautious about making such changes.
Other users (and management) can easily view change history and
deleted records. Preventing problems if far more effective than
curing them.
My 2 cents worth.
-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com
comments inline.
"(PeteCresswell)" <x@y.Invalidwrote in message
news:ru********************************@4ax.com...
Per tina:
create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base
for
all other queries.
I think you guys are winning me over.
The base query idea was the tipping point. I could use a
Find-And-Replace
utility to just change all affected table names to the base query name.
Main remaining problems that I can think of are:
--------------------------------------------------------------------------
----
- The Admin screens where a user might try to add a lookup table row whose
unique name is already there in one or more "deleted" rows.
as long as there is a unique index on the field which holds the name values,
the user won't be able to do it. if there's a possibility that a record
could be "deleted" and then legitimately need to be "re-added" as some later
time, just write a procedure in the form's Error event to trap the
"duplicate value error" which will be generated on the unique table index.
then automatically remove the flag from that existing record and requery the
form's Recordset to display it; or you can ask the user for confirmation
first, then do it.
>
- Some sort of pseudo RI issue where a user "Deletes" a lookup table row
that is
used by one or more non-deleted other table rows - causing various
queries to
return Null for the looked-up value.
if relational integrity is enforced, that can't happen. if users are
sophisticated enough to question why a "lookup" table record can't be
deleted, especially with hundreds of thousands of records in the data
tables, then they should also understand the explanation.
>
My kneejerk is to just not implement the flag system for lookup tables and
live
with it if a user trys deleting a row that's used by a "Deleted..." record
somewhere.
well, i'm assuming you've given user the ability to "clean up" the lookup
tables because they don't want to see obsolete choices in combobox droplists
and/or listboxes. to me, the bigger issue is how to prevent data entry users
from *choosing* (even by accident) obsolete droplist options because those
items can't be deleted. if users can only *look* at historical data but
never change it, it's easy enough to flag and hide "deleted" records in data
entry form droplists. but if historical data *can* be changed, it's a
tougher issue. in that case, probably the easiest solution (from a
programming standpoint) would be to flag the records as obsolete and call a
global function to check selected options on all combobox/listbox controls'
BeforeUpdate events and block selection of obsolete values, with a msgbox
for the user.
--------------------------------------------------------------------------
----
>
I'm thinking a field named "DeleletedAt" - which would be Null or contain
a
timestamp and "DeletedBy", which would be Null or contain a LAN UserID.
sure, why not, if it's important to track who deleted a record. just
remember that using two fields in the flag doubles the work of removing the
flag to "re-add" a record, if/when necessary.
>
I'd also think that the flag sb used only at the top of hierarchical
relationships. No sense "Deleting" child records if the parent is
flagged
as deleted because the user will never see same.
true enough. you'll have to analyze the business process (if you haven't
already) from the standpoint of determining whether specific child records
ever need to be "deleted", even though other child records and the parent
should remain active.
>
I would hope that there's no perceptible diff between IsNull(DeletedAt)
and IsDeleted=False. Anybody know? - although I guess I should set
up a
test table with a few hundred thousand recs and try it either way....
if IsDeleted is a boolean (Yes/No) field, i don't see a difference. if
you're concerned about a boolean field somehow reading as Null at the table
level, then you could set the default value of the field to False. then,
from the point of creation, the field will have a True or False value -
unless you specifically set its' value to Null (haven't tried it, i don't
know if that's possible).
hth
--
PeteCresswell
(PeteCresswell) wrote:
Per tina:
create a set of
base queries, one for each *data* table, with the Deleted flag set
to Is Null or False or whatever is appropriate. use those queries
as the base for all other queries.
I think you guys are winning me over.
The base query idea was the tipping point. I could use a
Find-And-Replace utility to just change all affected table names to
the base query name.
Side issue butt-in:
This is why I dislike prefixing tables with "tbl". If I were doing what you are
talking about I wouldn't have to find and replace all affected table names. I
would simply rename the table and create a query with the same name that the
table formerly had and I'm done. You don't say that you yourself use the "tbl"
prefix, but the reference to find and replace implies that.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
"(PeteCresswell)" <x@y.Invalidwrote in
news:gm********************************@4ax.com:
Per David W. Fenton:
>>The things you describe in the UI sound horrid to me. If they
should be allowed to do it, then let them do it and don't make
them feel bad about it.
Me too - but Plan A's multiple prompts were first thought of by
one of my users who just *had* to have it exactly that way.... So
I stuck with it....
I've tried the IsDeleted thing in one application.
Might do it again... might not. What I found is that the
added complexity propagates through the app to a greater degree
than one might think.
It all depends on how you implement it, I think. If you're afraid of
losing real data from mistaken deletes, then it's far preferable.
e.g. Referential Integrity raises it's head when the user goes to
delete a lookup table item that they *know* isn't being used by
anybody. Also some users doing ad-hoc reporting will not know
about it and their reports will be skewed accordingly.
Well, I would never let anyone but an administrator maintain lookup
lists, at least in regard to *deleting* existing items.
Things like that and having to make sure every single query looks
at IsDeleted put me off of it - and steered me to the archive
strategy.
That can be *very* easily fixed with one query and a search and
replace program:
1. create a query that returns all the fields and records of the
table with your delete flag, but filtered to omit deleted records.
2. with your search and replace program, you can do one of two
things:
a. replace all instances of the table with the query, OR
b. replace tablename with queryname As tablename.
The latter is a bit harder, as you want to do it only in the FROM
clause, but it's pretty easy with Speed Ferret.
You get 600-700 reports in an app and sooner or later, I'm going
to forget about IsDeleted and the results are going to pass
testing because the situation wasn't encountered and the reports
going to be wrong sometime in the future.
Not if you do it my way.
Another approach that's even easier would be to rename the linked
table, and then give the query the old name of the linked table --
instant filtering for all existing objects.
Having said all that, you might be right. Certainly I haven't
been able to come up with anything better. The current app is
pretty straightforward and I could probably confine use of an
IsDeleted switch to a single table.
I don't mean to completely minimize the complexities of it, but I
think in an app where users need to be able to delete data that
could be important, it's much easier to implement a delete flag than
to archive the data, in my opinion.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"(PeteCresswell)" <x@y.Invalidwrote in
news:ru********************************@4ax.com:
Per tina:
>create a set of
base queries, one for each *data* table, with the Deleted flag set
to Is Null or False or whatever is appropriate. use those queries
as the base for all other queries.
I think you guys are winning me over.
The base query idea was the tipping point. I could use a
Find-And-Replace utility to just change all affected table names
to the base query name.
Main remaining problems that I can think of are:
-------------------------------------------------------------------
----------- - The Admin screens where a user might try to add a
lookup table row whose
unique name is already there in one or more "deleted" rows.
- Some sort of pseudo RI issue where a user "Deletes" a lookup
table row that is
used by one or more non-deleted other table rows - causing
various queries to return Null for the looked-up value.
My kneejerk is to just not implement the flag system for lookup
tables and live with it if a user trys deleting a row that's used
by a "Deleted..." record somewhere.
-------------------------------------------------------------------
-----------
Or, in your admin interface, have the pseudo delete check for
related records and prohibit it if there are related records.
I'm thinking a field named "DeleletedAt" - which would be Null or
contain a timestamp and "DeletedBy", which would be Null or
contain a LAN UserID.
I just use the Updated and UpdatedBy fields used for all edits --
the only situation where I can see needing separate data is for
restoration if you want to maintain the edit trail.
I'd also think that the flag sb used only at the top of
hierarchical relationships. No sense "Deleting" child records if
the parent is flagged as deleted because the user will never see
same.
Exactly, but it does add overhead in that you may need to filter
some child recordsets by data that's found only in the parent
record.
I would hope that there's no perceptible diff between
IsNull(DeletedAt) and IsDeleted=False. Anybody know? -
although I guess I should set up a test table with a few hundred
thousand recs and try it either way....
I've used both because of different requirements in each app. The
biggest app I've used it in has 350K records in the top-level table
(the one where the deletions are done), and I use the two fields to
do it (a "deleted in favor of" field with a PK of another record,
and a delete date field; the former can be blank, so I use the
deleted date as the indicator; the record has Updated and UpdatedBy
fields used for all edits, and I use that to indicate who did it). I
haven't used the delete flag in any but relatively small apps (a few
thousand records), so I don't have any real base of comparison.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"tina" <no****@address.comwrote in
news:J%******************@bgtnsc05-news.ops.worldnet.att.net:
well, i'm assuming you've given user the ability to "clean up" the
lookup tables because they don't want to see obsolete choices in
combobox droplists and/or listboxes. to me, the bigger issue is
how to prevent data entry users from *choosing* (even by accident)
obsolete droplist options because those items can't be deleted. if
users can only *look* at historical data but never change it, it's
easy enough to flag and hide "deleted" records in data entry form
droplists. but if historical data *can* be changed, it's a tougher
issue. in that case, probably the easiest solution (from a
programming standpoint) would be to flag the records as obsolete
and call a global function to check selected options on all
combobox/listbox controls' BeforeUpdate events and block selection
of obsolete values, with a msgbox for the user.
That indicates to me that you'd need a deleted flag and an "active"
or "obsolete" flag, which would mean different things. I'm not sure
I'd ever bother with that -- I only ever use an Active flag in
lookups -- but it's conceivable that distinguishing the two would be
valuable.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Per Rick Brandt:
>This is why I dislike prefixing tables with "tbl". If I were doing what you are
talking about I wouldn't have to find and replace all affected table names. I
would simply rename the table and create a query with the same name that the
table formerly had and I'm done. You don't say that you yourself use the "tbl"
prefix, but the reference to find and replace implies that.
Right. I *always* prefix my table names. "tbl..." "tlkp..." "zmtbl..."
"zstbl".
I would explicitly not want a situation where renaming a query to a table name
would work. Tables are tables. Queries are queries in my book and I find
real value in glancing at a name in the code and knowing the diff. Kind of
like scoping prefixes on variables.
--
PeteCresswell
well, i've used only one flag in lookup tables in those situations, but
whatever suits the op's purposes is the best way to go, be it one flag, or
two, or ten.
hth
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"tina" <no****@address.comwrote in
news:J%******************@bgtnsc05-news.ops.worldnet.att.net:
well, i'm assuming you've given user the ability to "clean up" the
lookup tables because they don't want to see obsolete choices in
combobox droplists and/or listboxes. to me, the bigger issue is
how to prevent data entry users from *choosing* (even by accident)
obsolete droplist options because those items can't be deleted. if
users can only *look* at historical data but never change it, it's
easy enough to flag and hide "deleted" records in data entry form
droplists. but if historical data *can* be changed, it's a tougher
issue. in that case, probably the easiest solution (from a
programming standpoint) would be to flag the records as obsolete
and call a global function to check selected options on all
combobox/listbox controls' BeforeUpdate events and block selection
of obsolete values, with a msgbox for the user.
That indicates to me that you'd need a deleted flag and an "active"
or "obsolete" flag, which would mean different things. I'm not sure
I'd ever bother with that -- I only ever use an Active flag in
lookups -- but it's conceivable that distinguishing the two would be
valuable.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
well, that's programmer preference. neither way is "better"; applying a
standard of naming conventions consistently in a given database is more
important than what that particular standard happens to be. i personally
prefix my objects with tbl, qry, etc, too; and, of course, if you decide how
to handle the "delete vs hide" records issue before beginning physical db
development, then the find-and-replace issue does not arise. we all live and
learn in the game of database development, and refine our personal
development habits over time according to our individual experiences of what
"works best".
hth
"(PeteCresswell)" <x@y.Invalidwrote in message
news:6s********************************@4ax.com...
Per Rick Brandt:
This is why I dislike prefixing tables with "tbl". If I were doing what
you are
talking about I wouldn't have to find and replace all affected table
names. I
would simply rename the table and create a query with the same name that
the
table formerly had and I'm done. You don't say that you yourself use the
"tbl"
prefix, but the reference to find and replace implies that.
Right. I *always* prefix my table names. "tbl..." "tlkp..." "zmtbl..."
"zstbl".
I would explicitly not want a situation where renaming a query to a table
name
would work. Tables are tables. Queries are queries in my book and I
find
real value in glancing at a name in the code and knowing the diff. Kind
of
like scoping prefixes on variables.
--
PeteCresswell
(PeteCresswell) wrote:
Per Rick Brandt:
This is why I dislike prefixing tables with "tbl". If I were doing
what you are talking about I wouldn't have to find and replace all
affected table names. I would simply rename the table and create a
query with the same name that the table formerly had and I'm done.
You don't say that you yourself use the "tbl" prefix, but the
reference to find and replace implies that.
Right. I *always* prefix my table names. "tbl..." "tlkp..."
"zmtbl..." "zstbl".
I would explicitly not want a situation where renaming a query to a
table name would work. Tables are tables. Queries are queries in
my book and I find real value in glancing at a name in the code and
knowing the diff. Kind of like scoping prefixes on variables.
I don't prefix variables either. To me, such naming conventions are no
different than when novices to database design build "smart keys" where multiple
pieces of information are combined into a single field. Objects already have
meta data that is easily available. I see no point in trying to embed that same
data into the name of the object.
Having said that, I make no value judgment about developers who use these
conventions. I just personally don't see the advantage that they claim is there
and see many advantages to NOT using them. It was no surprise to me that the
use of such is now being discouraged in the dot-net world. Microsoft was
largely alone in their common usage to begin with.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Hello,
As other contributors have stated, Referential Integrity
should protect the most critical of deletes. Because you can never
delete a customer that you have ever done business with, I follow David
Fenton's idea. On our Customer screen a user can set a customer to
Inactive. This keeps down the clutter in the Customer combo boxes. (In
our case 200 customers with only 30 active) Simply query for [Active]
only. Next to every Customer combo box, however, I have a radio button
choice of "Active Customers" or "All Customers" in the event they need
to bring up and old, old invoice.
This leaves the issue of modifying lower level type records like
Customer Orders. A couple of years ago I added some Audit Trail
functions so we would know when the order was deleted or changed and
who did it. This is not to assign blame, but just to know whom to go
to ask why. Could be a very good reason for removing the order, but
we just want to know under what circumstances it happened. I prefer
using a separate table [Audit Trail] rather than adding the data to the
Order table itself. This is because there could be many changes
besides deleting the Order such as just changing the quantity, which
could happen several times over the life of an order.
This is some code I got off of our UG at the time. It's Access 2000.
Access provides the before and after values of controls if you check
them before the form is updated. This code should work as a baseline to
get you started.
Call it like this:
' ************************************************** **************
' Check for changed data 10/27/04
Private Sub Form_BeforeUpdate(Cancel As Integer)
CheckChangedData
End Sub
Of course you will have to write some reports to show when and by who
the data was changed. As the programmer here, this has worked well
to shift the blame from "Bad, bad, Database" to the actual user
who made the change
(Probably for a good reason)
' ************************************************** **************
' Check for changed data 10/27/04
Private Sub CheckChangedData()
Dim ctl As Control
Dim ControlName As String, strSQL As String
Dim ThisControl As String
Dim ThisType As Long
Dim TempOld As Variant, TempNew As Variant
DoCmd.SetWarnings False
For Each ctl In Me.Detail.Controls
' Only check data input controls
ThisType = ctl.ControlType
ControlName = ctl.Name
If ((ThisType = acTextBox) Or (ThisType = acComboBox) Or _
(ThisType = acCheckBox) Then
TempOld = ctl.OldValue
TempNew = ctl.Value
ThisControl = ctl.ControlSource
' Only save changes
If (TempOld <TempNew) Then
If (IsNull(TempOld)) Then
TempOld = "Null" ' Change Null value to text
End If
SQL1 = "INSERT INTO [Audit Trail] "
SQL2 = "( [Table Name], [ID], [Field Name], " & _
"[Old Value], [New Value], [Updated By], [When]
) VALUES "
SQL3 = "( Orders, " & Me.OrderID & ", '" &
ThisControl & "', '" & _
TempOld & "', '" & TempNew & "', " & _
PasswordID & ", #" & Now() & "# );"
DoCmd.RunSQL SQL1 & SQL2 & SQL3
End If
End If
Next
DoCmd.SetWarnings True
End Sub
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Brian Alexander |
last post by:
Hello;
I am trying to protect some global data, which may, in the future, be
accessed by threads. I'm not sure how to implement a locking mechanism
in python. Here is the idea I'm trying to...
|
by: Nate A |
last post by:
I am at the beginning stages of writing a massive database-connected business
management application using the .NET framework and am becoming worried
about the security of the application upon...
|
by: Pierre Phaneuf |
last post by:
At my workplace, we are in the process of migrating to a component
system similar to COM (but not COM itself, as we have some space and
portability requirements) that uses refcounting for resource...
|
by: Susan Baker |
last post by:
Hi,
I'm just reading about smart pointers..
I have some existing C code that I would like to provide wrapper classes
for. Specifically, I would like to provide wrappers for two stucts
defined...
|
by: Nick J |
last post by:
Hi,
How do I go about protecting my access database so that..
1) Nobody can edit any forms (reason being so nobody can remove the txt box
that states who developed it, change it to their name...
|
by: Roman Werpachowski |
last post by:
In a recent thread http://tinyurl.com/8n7fe I asked about preventing
the user from deleting the object pointed to by a pointer/reference.
Now I would like to ask about a different aspect of this...
|
by: Daniel |
last post by:
I have the following three classes
class A
{
public:
virtual void f() = 0;
};
class B: public A
{
|
by: Subhransu Sahoo |
last post by:
Hi All,
Can anyone explain me why the return type of new MyClass is
MyClass*. I think that it should be MyClass (*ptr) or MyClass**. Is
it dependent on the compiler implementation of new ?
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
