Archiving Records

[robsworld78]

I have a database that will have lots of entries so I want to archive files once a month and have it done with the push of a button.



First I want to ask if you think this should be done, the reason I'm thinking it should is so some of the queries don't have 1000's of records in them, I'm thinking it will make the system run quicker, is that true or no?

How many records does it take to start slowing a database down? Some queries have about 20 fields and lots calculated. My fear is after a year of data going in it will slow right down cause the queries will be so long.

I know its not good to move records to another table so I'm thinking of having a "yes/no" field in the orders table called "archived" which will be filtered in some of the queries so only current records for the month or whatever length I choose show up.

My question if I do this is, how would I go about setting it up so when I click a button on an employee form that has the orders in a subform it would then mark the yes/no field in all the records between a specific peried, maybe quarterly. It would be very painful if someone had to go through every order manually and click the yes/no field. I want that field checked automatically for all the records in that time frame.

Does this sound doable and is it worth all the trouble to do it?

[SoftwareMatters]

Archiving is definitely worth doing if you have 10s of 1000s of records or more but not always essential. There are other factors that will affect the speed such as how compicated the database is, how many users there are type of network.

When some of my databases get big I make an archive copy and then delete all the records I don't want form the current database. This way I can still access the archive if need be.

RE: yes/no field you could use a date field that say filters everything older than 1 month. This way you wouldn't need to click a button.

[robsworld78]

I don't know how many it will climb to but I can see it adding up over time. The database is quiet simple I believe and there will only be one user at a time. I like your idea of just coping the database and emptying it, never thought of that. I think that's the approach I'll take.

Is it possible to animate that process?

Can I have a button that does a backup and then empties out the database?

[SoftwareMatters]

Yes you could have a button to automate the backing up process but this would take a fair bit of programming.

To be honest if the database is fairly simple and there is only 1 user I don't think you will be at risk of it becoming too slow aslong as you run a compact/repair daily.

If you are really keen to archive/hide some of the data I would probably filter the old records using a date field.

[robsworld78]

I might look into the automating process but only once I get everything done. The reason I ask so much is because I won't be using the database, I'm actually building it for someone else and charging them for it so I don't want them coming back in a year saying it runs like crap now. I want it to always run as it does while I'm testing it with limited data. I don't really want to tell them to do the emptying of database if necessary. That's why I'm thinking automated it looks more professional.

I'll add the compact to the closing of the database, it will be closed nightly so it can compact at that time. Do errors every result from compacting the database?

The reason I'm all nervous is I built one database for some before, it was for tracking vehicle expenses and MPG, the database worked great then one day all these errors starting popping up when forms were opened, and nothing was ever done to edit the database.

[June7]

I have a project that links to old dBase tables. One of these tables is almost 70000 records. I see some lag in performance for filtering and sorting but not terrible.

If you 'archive' and users want to view this old data, will they know how to get to it?