Access 2003 archiving

[Guest]

How do I archive items withing a database? I want to take items that have a
term "rescinded" on them and pull them into another database for archives. I
can not figure out how to do this. Please help.

 

[Arvin Meyer [MVP]]

Write a query like:

Select * From YourTable Where Somefield = "rescinded";

You can change it to a make table query and append those records to a table
in this or another database, then change it to a delete query and delete
them from the current table. Delete the query when you are done and compact
the database.

 

[Guest]

Arvin is certainly right in how to do it; however, consider not doing it at
all. Some day you will be asked to compare and contrast the archived data
with current data. This would entail a link between two databases then a
union query to join the two tables. That could be very slow and inefficient.
Also if you make a change to your "active" table, such as adding a field or
increasing the field size, you must remember to make the same changes to your
archive table.

Unless you are starting to bump up against the 2 gb mdb file size limit or
performance is getting worse, I recommend not archiving data. Archived data
is often unusable data.

 

[Tom Wimpernark]

jerry

you're a wimp dog; partitioned tables aren't that difficult
and because Access MDB indexes suck a big fat dick; yeah I'd really
reccomend pulling it into another table

Partitioning isn't that difficult

But you really should be using SQL Server, Access MDB is obsolete and it has
been for a decade

 

[George Hepworth]

Aaron Kem.pf is posting under a new alias.

jerry

you're a wimp dog; partitioned tables aren't that difficult
and because Access MDB indexes suck a big fat dick; yeah I'd really
reccomend pulling it into another table

Partitioning isn't that difficult

But you really should be using SQL Server, Access MDB is obsolete and it
has been for a decade

 

[David W. Fenton]

How do I archive items withing a database?

I never archive data.

Why do you want to?

 

[Guest]

I certainly hope so. One like him is enough.

 

[Guest]

I certainly hope so. One like him is enough.

 

[Arvin Meyer [MVP]]

As others have suggested, you may not need to archive at all. I do archive
history data in cases where a history of costs or prices may be necessary.
Those archives save the key and allow multiple records using the key. I
occasionally archive when the data is no longer needed. For instance, I
archive building cost data for subdivisions that have been sold out for more
than 2 years. The first year is data which may be needed for warranties. The
second year, is a buffer year. Then I simply move the a copy of the entire
database to an archive folder and delete the unnecessary data in the
original.

Generally, you should not store an archive table (unless it's a history
many-side table) in the same database. That would be a violation of
relational rules. Before I'd do that, I'd add an "archive" (Boolean or
yes/no) field that would mark the row to be visible or not based upon query
criteria.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com