deteted records
sheila sims
records, so I deleted the others...now I find that the
basic data base has also been altered in the same way!!
Is there anyway I can retrieve the deleted data?? or
return to the original data base pre my query alteration.
Thanks
Sheila Sims
Ken Head
group;*access* or go to www.pksolutions.com
John Vinson
wrote:
This is how queries work. They are a "window" onto the data in the
table; if you edit or delete a record in a Query, it edits or deletes
the data in the table - the query has no independent existance.
There is no "undelete" feature in Access. If you have not entered any
new data (which will very likely have overwritten the
deleted-but-still-on-disk old data) or compacted the database (which
will have permanently and irrevokably deleted it), you can use a third
party data salvage service. The best I know is Peter Miller's:
http://www.pksolutions.com.
It's VITAL... not at *all* optional... to keep backups of Access
databases. Not only can records be edited or deleted by mistake, but
databases can become corrupt. The only database that you needn't back
up is one that you don't mind recreating from scratch at any time.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Peter Miller
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
microsoft.public.access.queries:
>The only database that you needn't back
>up is one that you don't mind recreating from scratch at any time.
Very well put.
Peter Miller
PK Solutions
_____________________________________________________
For Microsoft Access related tools and services,
including our Data Recovery Rescue Service for
Microsoft Access, please visit our site (below)...
_____________________________________________________
www: www.pksolutions.com
e-mail: pmi...@pksolutions.com
Tel: +1 (800) 987-7716 Fax: +1 (619) 839-3900
_____________________________________________________
John Vinson
<pmi...@pksolutions.com> wrote:
>On Fri, 12 Jul 2002 18:12:56 -0600, John Vinson
><jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
>microsoft.public.access.queries:
>
>>The only database that you needn't back
>>up is one that you don't mind recreating from scratch at any time.
>
>Very well put.
Thanks Peter. Fortunately there will be plenty of folks who fail to
observe this dictum, and your wonderful service will continue to find
customers... <g>
Peter Miller
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
microsoft.public.access.queries:
>Thanks Peter. Fortunately there will be plenty of folks who fail to
>observe this dictum, and your wonderful service will continue to find
>customers... <g>
John, its interesting that you say that. In the early to mid
nineties, it was not unusual for clients to come in for recovery
assistance because they had no backup regimen, but for more than five
years now, almost every client we have *does* have a backup and does
have a decent backup regimen. Its not lack of backups that brings
people to a recovery company for assistance - its lack of *good*
backups.
In the vast majority of cases we see, people revert to their latest
backup when corruption prevents usage of their current file, only to
find that the backup is also corrupted. Stepping back to previous
backups often (but not always) reveals that the corruption has been
present for some time.
The moral? You (not you John, but the general reader) should *never*
assume that because you religiously perform backups that you have
covered yourself. Your backups are worthless unless you periodically
verify their validity. It is essential to periodically restore a
backed up database to a test directory and then attempt to open and
step through it. Many people will be surprised to find out that their
backups are not what they thought they were.
There's two primary reasons for bad backups.
The first is that it is quite possible for people to continue using a
corrupted database without running into the corruption. Just because
the file worked yesterday or the day before doesn't mean it didn't
contain corruption then.
Second, even if the file only becomes corrupted on the day it was
detected, and prior to that day, the file was in perfect working
order, the backups may still be corrupted if the backup was performed
while the file was in use. If Access uses a database in a multi-user
fashion, it (being a file-server based rdbms and not client-server)
must leave the file open to other users for sharing, and so a mediocre
backup package (and let's face it, most of them are mediocre) will
simply see whether it can gain read access to the raw file, which of
course it can, before assuming that it is a good and clean file to be
backed up. Better backup programs will test to see whether they can
gain exclusive write access to the file, in which case they can detect
that the file is still in use and not back it up, but this is rare.
Besides, such superior backup programs do not in any way provide
better Access backups - they simply refuse to back up a file that is
in use. Either way, you're left without a good backup. Its important
to point out that even if all your users have successfully exited the
database, this does not necessarily mean that the db is no longer in
use. A failed connection earlier in the day may still be 'open'
according to the file server, even though all client pcs have
correctly shut down the db app for the night.
Anyway, hopefully this info is in some way helpful in encouraging
people to consider their backup strategies more comprehensively and
thoughtfully than simply checking to see whether they have one.
John Vinson
<pmi...@pksolutions.com> wrote:
>Its not lack of backups that brings
>people to a recovery company for assistance - its lack of *good*
>backups.
Thank you again, Peter - saved for future reference. An excellent (and
rather scary) summary. I wonder if you might want to see about getting
a copy of this onto http://www.mvps.org/access or one of the other FAQ
sites.
Tom Ellison
databases, this is critical.
Next, what procedures should we follow in testing for hidden corruption?
Almost every long term Jet back end we have had has had a problem like
this - where the corruption is in several weeks worth of backups before it
rears its head. In fact, this is approximately an annual event where
there is 10 - 15 hours a day of posting to the database from multiple
users.
Our solution has been to migrate the back end to MSDE. This solves both
problems (pretty much, I did have one hidden corruption with MSDE as
well). It backs up the database "between transactions" so there is
integrity. The alternative we have used with Jet is to use FMS products
for backup and for integrity testing. (No, I'm have no affiliation with
FMS, I just use the products.)
Thanks so very much, Peter!
Tom Ellison
Microsoft Access MVP
Tom Ellison
July 12 in this newsgroup. His posting July 14 is of critical
importance to anyone with a production Jet database.
Peter Miller
wrote in microsoft.public.access.queries:
>Next, what procedures should we follow in testing for hidden corruption?
>Almost every long term Jet back end we have had has had a problem like
>this - where the corruption is in several weeks worth of backups before it
>rears its head.
In general, I recommend the following:
1) whenever you are prompted to repair a file, make a copy of the
corrupt file and put it aside. Its quite possible your actions from
this point on will only make matters worse, so an early copy of the
damaged file is an important asset.
2) try to repair the file. If success is reported, still *do not
trust* the repaired file. Create a new database and import everything
into the new database. Make sure all objects import cleanly. Note
that this advice is critical for earlier versions of Access (where a
repair was separate from a compact). For latter versions, checking
for the presence of a new hidden system table called MSysCompactErrors
and a review of what it contains is important, especially if Jetcomp
was used as part of the repair, and an error msg was reported. This
table stores information on what objects and or records were killed in
the repair process in order to salvage the rest of the file. If
anything critical is listed here, you'd want to send your original
unrepaired db file to a recovery company like ours for proper
recovery.
3) To ensure corruption is not present in the data in a db that does
not actually report corruption, you can try simply looping code that
i) getting a record count from each table
(ie, using a query like 'select count(*) from " & tablename)
ii) opens an editable recordset on each table and tries to jump
to the last record
iii) opening a recordset on each table with memo fields and
searching for any particular value (it doesn't matter what the
value is - the point is the search, not the results).
Its important to remember that corruption in tables often occurs in
the table indexes. When this occurs, your data is fine, but if you
try and search that data in any fashion that utilizes a bad index, you
get errors. Simply importing such a table to a new db will solve such
problems by recreating the index as part of the import process. In
other words, there's no need to repair a bad index. You simply have
it re-create itself as part of the import process.
>Our solution has been to migrate the back end to MSDE. This solves both
>problems (pretty much, I did have one hidden corruption with MSDE as
>well). It backs up the database "between transactions" so there is
>integrity.
Yes, this works, as does any client/server backend from any vendor.
The key is the architecture change - not the product.
>The alternative we have used with Jet is to use FMS products
>for backup and for integrity testing. (No, I'm have no affiliation with
>FMS, I just use the products.)
Agreed, FMS's products are totally respectable, although sometimes
people make the mistake of thinking that their Agent product has a
repair capability independent of Access/Jet, which is not the case.
FMS's Agent product can only repair files that Access/Jet could
repair. The real value to the product comes in its scheduling
capabilities, which, it should also be noted, are available from other
vendors too. But you won't go wrong by relying upon FMS for solid
Access utilities. (I also have no affiliation with Luke and FMS).
Tom Ellison
One small change. I believe the search (your iii) needs to be performed on
every memo field, not just "on each table with memo fields". Memo fields are a
major source of the corruption we see. If you have a critical database that
must be dependable, you should consider not having memo fields.
Tom Ellison
Microsoft Access MVP
Peter Miller
wrote in microsoft.public.access.queries:
>One small change. I believe the search (your iii) needs to be performed on
>every memo field, not just "on each table with memo fields".
That is indeed what I meant. Search every table that has a memo
field, on every memo field in such tables.
>Memo fields are a
>major source of the corruption we see.
Actually, ole fields, and in fact any non-trivial file format (ie, any
non-numeric data type that can exceed 255 bytes) have the same
identical issues, because they are all stored independently of the
given tables underlying data pages. As you probably know (but the
general reader may not) all memo, ole and such data types from all
such fields in all tables are stored outside of all such tables, and
are actually stored in the same single general purpose hidden system
pseudo table, with a simply pointer structure into that storage space
for each value. A memo field from field1 in tableA may be stored next
to an ole value from field5 in tableC which in turn is stored next to
a snippet of a form's definition, which is stored next to another memo
value from still another table. This is all fine, except of course,
if the linkage between the underlying records and this general
extended storage space is broken in any way, in which case the whole
system starts to break down.
>If you have a critical database that
>must be dependable, you should consider not having memo fields.
Well, if you have a 'critical system that must be dependable', you
shouldn't be using Jet to store it, but yes, avoiding memo/ole fields
can lead to a lower chance of corruption. In general, for fields that
will store text, the user should seriously consider whether the field
absolutely needs greater than 255 characters. The downside to using
memo fields is not just instability of this fashion, but also that
they are slower to use, search, etc, that there are limitations on
their use (certain operations possible on other data types don't work
on memo fields), and that there are bugs in those features that are
supposed to work.
An example of such a bug?
In Access XP, create a new table (say TableA) with two fields. Field1
is a memo type. Field2 is a long integer. Save the table and don't
create any indexes. Now create a second table (say TableB) which has
one field (Field3) also a long integer. Save this second table
without creating any indexes. Add a record to TableA with field1 set
to any string of words (length is not important) and field2 set to the
value one (1). Create a record for TableB with a field3 set to a
value of one (1). Create a query based on tableA and tableB. Link
field2 in tableA to field3 in tableB. Display just field1 (the memo
field) and group by this value. Display the query. You would expect
to see a single record with just the value you typed in to the memo
field, but you will instead see garbage. Access doesn't correctly
handle grouping by memo fields on non-indexed tables in multi-table
queries, among other things.
Peter Miller
<pmi...@pksolutions.com> wrote in microsoft.public.access.queries:
>any non-trivial file format
er, that should have read 'any non-trivial data type'...
Tony Oakley
Thanks for pointing that out Tom, noted.
--
Tony Oakley (MVP)
RF900RT