I have a database that has been in use for some years. Within this
there are tables for Orders, OrderDetails, Deliveries and
DeliveryDetails. The user now wants for archive deleted Orders and/or
Deliveries. At present they are simply deleted. Once archived he
wants the ability to restore selected 'archived/deleted' Orders or
Deliveries.

There appear to be two approaches:
1 to set up archive tables with the same structure at the originals.
When deleting records, append them to the archive tables and delete
them from the original tables

2 add an extra Boolean field in the tables to indicate whether the
record should be regarded as 'deleted' or not.

The first seems to be straightforward except for the fact that the
OrderID in the Orders table is an Autonumber and this is the
published, visible Order number. I know, I know I should not have
used an Autonumber in this way but I have. Archiving appears to
present no problem but I am concerned about restoring from the
archive. Is it possible to re-insert the archived records back into
the original tables whilst maintaining the original Autonumber?

The second approach is simple in one respect but would require me to
make sure that all appropriate queries were changed to filter out the
'deleted' records. There are many such queries and I need to be sure
that I change them all. Seems a lot of work!!

I would welcome any thoughts about which is the better approach. Or
in fact whether there are still better approaches.

Jim

Most experienced Access users suggest that you add a "status" field, in
which you mark the record as active or inactive -- it can, if you wish, be
used to indicate more detailed status information. I've never needed it to
have any more than basic active/inactive status in work that I've done.

A counterexample would be if you were dealing with so many records that
performance became a problem because of the archived records. In that case,
a separate table for archived records would make sense. You might want to
consider putting the table of archived records in a separate database, too.

- -
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Jim Devenish" <internet.shopping (AT) foobox (DOT) com> wrote

Jim Devenish wrote:

IMO, if you need to reinsert the records then what is the purpose of
archiving? You need to be sure, as well, that the relationships remain
instact as well when passing records back and forth between tables. You
might also want to remove the autonumber and make it a number instead
and create a function to update the value if you use the first option..

For the second option. Maybe a table name is Customer. Create a query,
drag the * to it, then do a Where Not DeleteFlag. Then search your code
for "FROM CUSTOMER", "JOIN CUSTOMER", and "INSERT INTO" and change
Customer to qryCustomer.

You should be able to iterate thru the forms and reports recordsource
and the queries SQL to check for the same phrases.

"Jim Devenish" <internet.shopping (AT) foobox (DOT) com> wrote

I have done the 1st, using a archive table in a 2nd mdb. Auto numbers will
cause a problem on restoring. But, why would you need to restore archived
records? If it is for reporting, point the reports to the archive. The only
benefits are reporting is quicker on the active data and the active MDB is
smaller.

The 2nd method could be handled by renaming the table and adding a query
with the old table name that filters out the archived records.

Hi Jim,

There are in fact six approaches to achieving what you want to do, and
they come out of data warehousing techniques. The six approaches are
referred to as dimension types, but in everyday terms this means how
your table works and is structured. They are:
Type 0: No Control at all over the data (not recommended for your
needs)
Type 1: Sometimes called a 'Punch Through' dimension. This keeps only
the current valid data and no history
Type 2: (My favourite) This keeps a history in the same table as the
active data. This is done by the inclusion of a version number field
and sometime also a valid from and valid to pair of date fields. If
date fields are used then the current 'active' record has a null for
the valid to field.
Type 3: Limited history based on adding additional columns for storing
old data. The length of history is limited to the number of extra
columns for holding the older data that you provide.
Type 4: (my second fave) uses two tables, one for the active records
with current data the same as a type 1 dimension, but with an added
extra table for storing the history. The history table uses a change
date field in addition to the same primary key field as the active
record table. Every change that is made to the 'live' table is
effectively 'logged' to the history table
Type 6: (note there is no Type 5) This is a hybrid dimension based on
the features of Type 1, 2, and 3. The table has valid from to fields
as well as version numbers, and also a field to mark the active
record. Extra columns can be added if needed to allow reference to
older versions of the record. A 'surrogate' key is used to
individually number each row, and can be used as a type of self join
in a type 3 column for locating older data.

FWIW surrogate keys are a very useful concept in all these scenarios.
They are just an extra column with an autonumber field in these cases.

Hope this helps

The Frog

Thank you all for your helpful comments.

Salad and Ron question why I would wish to archive records if I
subsequently wish to restore them. Think of deletion in the way that
files are placed in a Trash can. Although they appear to have been
deleted that are simply placed in a different place from which they
can be retrieved.

I have since experimented with some SQL statements to copy, delete and
restore records. To place an Order into the archive:
INSERT INTO OrdersArchive SELECT Orders.* FROM Orders
WHERE Orders.OrderID =11800

The 11800 is merely illustrative!

Then one can:
Delete * From Orders Where Orders.OrderID = 11800

To restore this record:
INSERT INTO Orders SELECT OrdersArchive.* FROM OrdersArchive
WHERE OrdersArchive.OrderID =11800

The important aspect of this is that it re-inserts the record with the
correct AutoNumber, namely 11800. My concern about this approach was
ill founded. I should have done the experiment before posing the
question. Sorry about that!

With this in mind I am going to follow the first approach.

Anyway, thanks Ron for the suggestion, in the second approach, of
renaming the table and creating a query, with the original name of
table, filtering out the 'deleted' records. I have used a similar
approach in another context but it did not come to mind here.

Finally, thank you Frog. I had not realised there were so many ways
to skin a cat.

"Jim Devenish" <internet.shopping (AT) foobox (DOT) com> wrote

On test may want to do with the delete / re-insert system
1) Delete the record with the highest autonumber
2) Compact the database, I think Access will reset the autonumber
3) Add a new record, It should get the deleted autonumber, If it does you
will not be able to reinsert.

On Sep 21, 12:26*pm, Jim Devenish <internet.shopp... (AT) foobox (DOT) com>
wrote:
I do something similar in a database used to store employee training
records. If an employee leaves, the training records go into an
archive table. If they return someday, the archived records are
automatically brought back into the employee training database. Post
back if you want more details about the queries I run to archive and
restore the records. BTW, Larry's well authored comments about
archived data and performance are close to my philosophy. Also note
that users have an intuitive expectation that looking up archived data
will take longer than looking up active data.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

"paii, Ron" <none (AT) no (DOT) com> wrote in
news:i7d2pd$alp$1 (AT) news (DOT) eternal-september.org:

This used to be the case, but recent Jet 4 versions don't always do
this (I have no idea about ACE, though). You sometimes need to reset
the seed value in code. I find this fact quite annoying.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Tue, 21 Sep 2010 09:26:09 -0700 (PDT), Jim Devenish
<internet.shopping (AT) foobox (DOT) com> wrote:

I always do this approach. It's less work, much simpler even though
you have to change some queries. But then I would never mark those
records deleted either. I give the user the ability to filter by
date ranges and maybe default the date range to the last 30 days or
this calendar year or whatever.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/