Should I archive?

[C Brehm]

Am doing a database for a flea market.
Have a table [sales]
This table contains every item sold by multiple vendors.
Table can grow by 1000 records or more per day.
Has a field for vendor paid for item
Should I archive to a [sales paid] database when the vendors are paid for
these items or when [sales] gets to a certain number of records?

In other words when should I consider archiving a database?

 

[Nikos Yannacopoulos]

There is no recordcount limit in Access. The limit is the size of the
(.mdb) file storing the data, and that is 2GB. If you compact regularly,
you are unlikely to get anywhere near that in less than several years,
with a fairly simple and well designed database. My guess is you could
probably keep the current year plus a couple of years' history in your
production database, and archive a whole year (like three years ago or
so) at the beginning of a new one. Look at file size, it will give you
the answer.

HTH,
Nikos

 

[Van T. Dinh]

Personally, if I think I may need to look at the Records, I don't archive
them.

In some of my databases, I have an Archived flag in the Table so if the
users don't need to see these Records for day-to-day ops, they can mark
these Records as "archived" the users don't see them any more. I can also
use "clean-up" routines to mark them as "archived" if I think the users
don't need to see them. However, the Records are still there just in case.

The max size of the database is 2 GByte (A2000 & later) and that's a lot of
storage for a properly-designed database.

Remember we are dealing with relational database so if you archive (in the
sense of moving the Records to a different Table in a different database
file) Records in one Table, it is more than likely that you need to archive
related Records also.

Basically, if I use actually archiving (rather than flagging "archived"), I
hope that I never have to look up the archived Records in the archive file.