reasons to archive and how

[cporter]

In our old database we had to archive data yearly due to software
limitations. We generate close to 28,000 records a year in our
workorder file. Is there a good reason to keep this up in Access? The
only reasons I can think of would be to reduce the size of the tables
being searched, increasing the speed of the search, and being able to
lock completed records to prevent accidental changes. Are there other
reasons like file size limitations?


Is there a quick easy way to take the files between certain dates (easy
enough) and move them (erase from the old table) to a new table with
the records not editable?

 

[TC]

28,000 records is nothing. As long as your tables are properly
designed, Access can easily deal with hundreds of thousands of records
- or more! Like any serious database product, Access has special code
behind the scenes to index records efficiently. Doyubling the number of
recrds, /will not/ double the time to find any record; it might only
increase the time, by a very small value. Look up "B tree indexes" in
the unlikely event that you want more details on how a database product
can do that.

When an Access database has slow response times, it is almost certainly
because the tables are wrongly designed, or the SQL is inefficiently
written, or the system has not been designed to work efficiently over a
local network.

HTH
TC (MVP Access)
http://tc2.atspace.com

 

[John Vinson]

In our old database we had to archive data yearly due to software
limitations. We generate close to 28,000 records a year in our
workorder file. Is there a good reason to keep this up in Access? The
only reasons I can think of would be to reduce the size of the tables
being searched, increasing the speed of the search, and being able to
lock completed records to prevent accidental changes. Are there other
reasons like file size limitations?


Is there a quick easy way to take the files between certain dates (easy
enough) and move them (erase from the old table) to a new table with
the records not editable?

TC's right. An Access database is limited to 2 GByte (two BILLION
bytes) in any one database file - and you can use multiple database
files if you want. 2,800,000 records - a hundred year's worth - is
perfectly managable in Access, given proper indexing and query design;
I know of databases with tables ten times that size.

Regular backups and compaction are essential, of course.

John W. Vinson[MVP]

 

[Guest]

One great piece of advice I received about archiving: Archived data is dead
data. If you make changes to the database, for example put in a constrait,
the old data probably can't be imported back into the table for direct
comparison. Business rules change.

Once had a case where the archived data was stored on tapes and the company
didn't have that kind of tape drive anymore. Once we found a suitable drive
on eBay, a few of the tapes wouldn't read. After expending much energy on
extracting what data we could, it really didn't help much.

 

[Pat Hartman\(MVP\)]

My first job, over thirty years ago was with a large insurance company.
They kept 7 years of history. All on 9-track tape of course. Each time we
modified a current file format or added/deleted a code value, we needed to
modify 7 years worth of history tapes. It kept a couple of programmers
permanently busy.