recover deleted records from backup, best approach?

[DaveDiego]

I've had a user delete one of the client records, I do have a version of
the DB with all records intact before the deletion occured.

Whats the best approach to getting all the related records in each of
the tables? I have about 12 tables to put data back into and multiple
records for each. Would I need to make an append or update query for each
table?

[Pavel Romashkin]

Either that or you could build a query that joins all the related
tables, and select the information from all of them at once. Make sure
the query is updateable.

Pavel

DaveDiego wrote:

I've had a user delete one of the client records, I do have a version of
the DB with all records intact before the deletion occured.

Whats the best approach to getting all the related records in each of
the tables? I have about 12 tables to put data back into and multiple
records for each. Would I need to make an append or update query for each
table?

[Tony Toews]

DaveDiego <Th************************************@usenet1.wi ntermute.cx> wrote:

I've had a user delete one of the client records, I do have a version of
the DB with all records intact before the deletion occured.
So how did they delete so many records? Do you have cascade delete turned on? If so
se below.
Whats the best approach to getting all the related records in each of
the tables? I have about 12 tables to put data back into and multiple
records for each. Would I need to make an append or update query for each
table?

You'd have to append from the backup MDB into the new MDB as the records don't exist
in the new MDB.

================================================== ===

Why I don't like cascade updates and deletes
Doing a lot of cascade updates can be more susceptible to performance and corruption
problems as cascade updates could need to lock a lot of pages. A table high up on the
relationships "food chain", for example, a customer table, could require many
thousands of invoices to have the customer foreign key to be changed if cascade
updates were allowed.

I also feel that, unlike Tom Ellison who eloquently disagrees with me, primary
autonumber keys should be used in all tables. Which are never visible to the user and
thus no cascading updates are ever required.

I also have an intense dislike of cascading deletes. <smile> Especially when a user
sees the system message they don't pay any attention to the extra wording which goes
along the lines of "(and associated records in other tables)" And it can be way, way
too easy to start deleting records from too many tables. For example you go to delete
a customer and, whoops, cascade deletes just removed all the customers invoice
headers and invoice deletes. Clearly that's an extreme example but is quite possible
if someone doesn't clearly understand what could go wrong.

So I just delete the records in the child table(s) myself as per the following code.

strSQL = "DELETE * FROM InvTransactionsDetailsDataEntry " & _
"WHERE itddeInvTransHeaderID=" & Me!ithID & ";"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "DELETE * FROM InvTransactionsHeader " & _
"WHERE ithID=" & Me!ithID & ";"
CurrentDb.Execute strSQL, dbFailOnError

where ithID is the tables primary key and is found on the form calling this code.

For ADO use CurrentProject.Connection.Execute strSQL, lngRecordsAffected, adCmdText
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

[David W. Fenton]

Tony Toews <tt****@telusplanet.net> wrote in
news:ip********************************@4ax.com:

I also have an intense dislike of cascading deletes. <smile>
Especially when a user sees the system message they don't pay any
attention to the extra wording which goes along the lines of "(and
associated records in other tables)" And it can be way, way too
easy to start deleting records from too many tables. For example
you go to delete a customer and, whoops, cascade deletes just
removed all the customers invoice headers and invoice deletes.
Clearly that's an extreme example but is quite possible if someone
doesn't clearly understand what could go wrong.

Cascade delete on a customer table when there are invoice records is
clearly a schema design error, in my opinion. Invoices are something
that can't be deleted, as you must have the transactional record, so
the parents of the invoices themselves can't be deleted.

But, there are plenty of cases where cascade delete is fine -- when
the child records are not part of any body of important information
that is independent of their role as child of another data record.
For instance, a client's inventory application (and antiquarian
music dealer, where everything is a one-of-a-kind object, one record
per item, no inventory counts) has key words attached to the parent
record. They recently went through all the inventory items that were
numbered under their old inventory system and created new records
with new inventory numbers copied from the old record (the reason is
that in the old system the numbers were of the format ALS-1234 where
1234 could also be used for INS-1234; this eventually caused
problems for them, so they wanted to get rid of them). Then they
deleted the old records. The key words for the old records were
cascade deleted, as they should be, because they were only a join
table between the inventory table and the key word lookup table.

I'm working on an app right now that has an events table for
tracking things that happen over time, and a related sub events
table that breaks down particular variable values at the time of
each event. If the parent event is deleted, the sub events should be
cascade deleted.

Now, that's very handy during development. I'm not sure whether I'll
leave it for production use, especially since when the app is fully
installed, it will be getting its data from an automated import
routine rather than from data entry. But it's certainly a case that
if the parent records are deletable, the child records have no
function other than as a part of the parent.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Mike MacSween]

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:ip********************************@4ax.com...

I also feel that, unlike Tom Ellison who eloquently disagrees with me, primary autonumber keys should be used in all tables. Which are never visible to the user and thus no cascading updates are ever required.
Do you mean that the child records would just be left in place without a
parent record, but invisible because no query would ever reveal them?

I've discovered a flaw in one of my designs that leaves me with students
who've never done a course. Although it's a flaw in MY design (code design
rather than schema) it still leaves me in the situation you appear to be
suggesting.

If so, that seems like bad design. But I expect it might depend on the
meaning of the data. As David points out later, deleting invoices is just
plain bad full stop. Including putting paper invoices through the shredder!!
I also have an intense dislike of cascading deletes. <smile> Especially when a user sees the system message they don't pay any attention to the extra wording which goes along the lines of "(and associated records in other tables)"

I think they're OK sometimes. But handle with care, yes. I interrupt them
sometimes with a more user friendly message which directly relates to the
data. "Do you realise if you delete this course you will also delete the
student records of every student who has attended it' or whatever.

I know some people never delete, just mark them as deleted. Can't see the
point really, if you don't need the data anymore then you don't need the
data anymore, but then.....!

Mike

[Bernard Peek]

In message <2g************@uni-berlin.de>, DaveDiego
<Th************************************@usenet1.wi ntermute.cx> writes

I've had a user delete one of the client records, I do have a version of
the DB with all records intact before the deletion occured.

Whats the best approach to getting all the related records in each of
the tables? I have about 12 tables to put data back into and multiple
records for each. Would I need to make an append or update query for each
table?

If you have a primary key in every table, or any no-duplicates index,
you could simply import each table. Records that already exist will be
dropped. This also copes with the possibility that more than one client
record was deleted.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[David W. Fenton]

"Mike MacSween" <mi***********************@btinternet.com> wrote in
news:40***********************@news.aaisp.net.uk:

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:ip********************************@4ax.com...

I also feel that, unlike Tom Ellison who eloquently disagrees
with me, primary

autonumber keys should be used in all tables. Which are never
visible to

the user and

thus no cascading updates are ever required.

Do you mean that the child records would just be left in place
without a parent record, but invisible because no query would ever
reveal them?

???

Have you ever tried changing an Autonumber value? You can't do it,
even in SQL.

CASCADE UPDATES is only valuable if the parent key field is
editable. Thus, with an Autonumber PK in the parent table, CASCADE
UPDATE serves no funciton.
I've discovered a flaw in one of my designs that leaves me with
students who've never done a course. Although it's a flaw in MY
design (code design rather than schema) it still leaves me in the
situation you appear to be suggesting.
With RI turned on, you can never get orphan child records.
If so, that seems like bad design. But I expect it might depend on
the meaning of the data. As David points out later, deleting
invoices is just plain bad full stop. Including putting paper
invoices through the shredder!!
Orphaned records has nothing to do with CASCADE UPDATE/DELETE.

Remember, CASCADE is something added onto referential integrity
enforcement, and doesn't apply until such time as you have RI turned
*on*. If it's turned on, you can't have orphan records.

The CASCADE settings have an effect only when the specific operation
for each cascade type is performed on the perent record:

1. UPDATE: if the parent value is updated, all child values are
updated to match.

2. DELETE: if the parent record is deleted, all child records are
deleted.

As explained above, Autonumbers make #1 unnecessary, since the
parent value *can't* be updated, so there is no chance of anything
needing to be cascaded. The second one should be used only when the
child records serve a function only as children of the parent, and
not in any other context.

Now, if your PK is *not* an Autonumber, but, say, a generated
sequence, then you *may* want to have CASCADE UPDATE turned on. But
if you don't, you *still* won't get orphaned records, because RI
will prevent you from making a change that will orphan those
records.

[]
I know some people never delete, just mark them as deleted. Can't
see the point really, if you don't need the data anymore then you
don't need the data anymore, but then.....!

You're confusing RECORD DELETED from the end user's point of view
with RECORD DELETED from the database's point of view. While those
can often be implemented as exactly the same thing, there are uses
to making physical record deletion an operation separate from
deleting records from the recordsets that the end users get to see.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Mike MacSween]

Sorry David, I didn't really understand any of that.

[David W. Fenton]

"Mike MacSween" <mi***********************@btinternet.com> wrote in
news:40***********************@news.aaisp.net.uk:

Sorry David, I didn't really understand any of that.

If that's true, then I'm glad I'm not one of your clients.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

"David W. Fenton" <dX********@bway.net.invalid> wrote:

Sorry David, I didn't really understand any of that.

If that's true, then I'm glad I'm not one of your clients.

Whoa, David. If Mike has never used non autonumber keys or cascade updates he'd
never know all these details. I did once on one of my first systems back in A2.0
thus I recall the issues.

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

[David W. Fenton]

Tony Toews <tt****@telusplanet.net> wrote in
news:8c********************************@4ax.com:

"David W. Fenton" <dX********@bway.net.invalid> wrote:

Sorry David, I didn't really understand any of that.

If that's true, then I'm glad I'm not one of your clients.

Whoa, David. If Mike has never used non autonumber keys or
cascade updates he'd never know all these details. I did once on
one of my first systems back in A2.0 thus I recall the issues.

Not knowing them and not understanding a fairly basic explanation
are two different things.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc