db Maintenance...sweeper

[nhcaver]

Ok, so I will admit, I am a newbie to Access and VBA, but I have reached a series of epiphanies, and am rolling up my sleeves to get down to business. However, taking into account the lack of computer savvy in my office, I am trying to make things as "automatic" as possible to remove ID10T user error.

My desired result is this:
To have a function that, after a certain amount of time and one other criteria, runs and deletes the records that I do not need to hold on to. My situation is this: I have my main table where I store the records. When actual physical records in the table are ready to be reconciled, I run an action "delete" query that removes the record from the main table, but just previous to the "removal" of the record, it appends it do a seperate table that the end users do not have direct access to (for running reports for my office's management). I would like it so that when a certain amount of time passes, there is a function that goes through and removes the record, whith a minimum of intervention of the end user. A function that will just tidy things up a bit. A "Sweeper" program.

Does anyone have a starting point for me?

Thanks!
Aaron

 

[Galaxiom]

Unless you are talking about millions of records I would start by not doing it, especially on a timer while users are in the system. If the process fails for whatever reason you could lose records or end up with multiple copies in the archive table.

Just add a field to the records to indicate they are archived and change your record source queries to select only those not flagged if the user is a normal end user. For your management reports include all records.

When you do eventually archive, run the sweeper routine supervised after doing a backup.

 

[gemma-the-husky]

to take this further

access is amazing at processing many thousands of records, and you dont generally have to worry about speed.

read about binary trees and b-trees if you want to get a technical feel for how indexing works - i should imagine access does something similar

however, it never harms to try and reduce the amount of data you retrieve. Thats why a lot of websites give you A - Z title selectors, or worse, category selectors, which are pretty un-user frindly at doing this.

however generally with historic data you will with either have

a) a date range
b) an account number
c) a year indicator
d) an uncompleted, or status flag

each or all of which you can include in an index, whereupon access will retrieve the data you want to see very efficiently.

As a matter of course you wouldnt need to archive stuff, and certainly not delete archived stuff, unless there was some other issue, or your files were just ridiculously big.

 

[nhcaver]

Thanks for the input

The reasoning behind using the sweeper is that we will quickly have thousands of records building up in the "deleted record table" due to the nature of what we are using this for, and based upon the disposition of certain records, there is simply no reason to hold onto them any more. I realize of course, due to the simple "yes/no" nature of the record, I could just go in and sort and then remove them. But for those that we have a responsibility of record retention for a specific amount of time, I thought that it might be useful to develop something that would see how much time has passed and then automatically delete those records that have expired...

And I think I just answered my own question...

This week has been full of Access eureka moments for me...

Thanks!

 

[The_Doc_Man]

nhcaver, I will add to what has been said.

First, don't archive that often. Mark the records as "obsolete" in some way and just don't select them in your working queries. (Which means you never use tables to feed anything that needs to be sensitive to record state.) I would not put an index on a field that is of type Yes/No because that is a wasted index. Very poor statistics on that, so you don't get much of a performance boost from such an index. But if you archive by dates, an index on a date field is absolutely reasonable and would give excellent selectivity.

Second, when you are going to archive, arrange for some DB down time on a predictable schedule. STICK TO THE SCHEDULE if you publish it.

Third, BACK UP YOUR DB before doing anything else. Alternative: Copy the original DB to a work area and do your work only on the copy. Don't replace the "live" copy until after you are satified that all of this was done correctly. There are two schools of thought on how to do this; I can't see a pragmatic difference between the two as long as you are careful.

Fourth, perhaps using Append or MakeTable queries, extract your data (the stuff ready to be archived) to your receiver table, which could even be in another database via linked table manager if you wanted. Which gives you a ready-made repository for up to 2 Gb of archived data separate from your "live" DB.

Fifth, with users still not onboard, run the delete query. Empty out what you need to empty out. Rule of thumb: If you didn't archive it, don't delete it. You mention that in your case, there might be some class of record for which deletion without archiving is a valid action. Most of the time I would say don't do it that way, but as long as you are (a) careful and (b) sure enough to bet the mortgage on your deletion, go for it.

Sixth, perform a database compaction and repair on that DB. That is the only way to shrink the size of a DB that has experienced lots of deletions.

Seventh, test that the DB is OK. You aren't done until you get through this step.

Eight, (optional step) replace your backup copy of the DB by copying the archived, compressed, repaired DB. Alternative, overlay the updated DB onto the working copy - see the earlier remark about which DB file you work on.

Ninth, open the floodgates to your users.

Now, how do you shut out users? I use a startup form that has code running underneath it. If the users try to get in during a maintenance period, I have the startup form detect that fact (by looking at some hidden table or a table that is read-only to the world, but which I can update) and if users are supposed to be locked out, do an Application.Quit on them.

Be SURE that if you do the startup-form method, that it won't do a QUIT on you.