How Do You Archive Records In a Database?? 3

[quest4]

What is the best way to archive records in a database which are two yeard old or older? How do you restore those records, in case you may need them? Thanks to anyone answering this.

 

[THoey]

You could always create an archive MDB with just the table structures, link to that MDB, and then write a query to move/delete the data. Terry M. Hoey

 

[quest4]

Thanks for the quick response. Interesting concept, I always thought you had to send it to a filat file, guess I was wrong. I guess I have one or two questions about this. First how do you link these two databases? I also wonder, can you have a table for each of the archived years? Thanks again for the quick response.

 

[quest4]

After looking at this, I don't think that the linked dbases are going to work for us in this situation. We need too keep separate tables or files for each year. When you linke dbases, it wants to link to "a table". The tables would continuously grow in numbers as the years past by. I can't see being linked to one table and having it work. Do you have any other ideas? Thanks again for the response.

 

[tempclerk]

You can link to all your tables, and use a union query if you want to use them all together again. Why do you need to keep each year separate, and how else were you thinking of accessing them again?

 

[quest4]

Thanks for the responsse tempclerk. The reason for separate years is just to make life easy for the payroll people. Usually they only need no year of data on an employee and with a hundred employees to look at I thought that one years worth of data at a time would be just right. I was also, not sure how yet, of appending the year to the back of the table or file name. The other idea I had was using flat delinited files, in the same directory, so they can be easily moved to different locations if needed. Also archive attendance records only need to be keep for 7 years and after that they can be trashed. Thanks again for the response.

 

[StarScream]

From what you just said, looks like you could keep everything in the one table. Once you move everything over to the archived db, just use a query to print out a specific year's data for a specific employee. No need for have different tables for every year.

You may end up having several archive dbs, though. IE - Arhive 1994-1997.mdb, Archive 1997-2000.mdb, etc. because you may max out the db with information. But this should work if set up right...

PJ

 

[StarScream]

Another thought....

If you do go this route, you can create a Master Archive db which can let you keep track of all your archive dbs. The Master db could be set up so you click on which year you are interested in and them bring up that database. Search for this on the website as I have seen several threads on how to do this...

Hope This Helps...

PJ

 

[quest4]

Thanks for the response StarScream, it sounds like it may have some possiblities. I did a key word search here and found only 1 or 2 thread that pretained to this, in Access, alot in SQL server, thoe. The archive is for a hundred or so employees and we are archiving their attendance data which is kept by the week. In the table we have the current year data and the previous year data. I would like this to be as automatic as humanly possible, because the accounting persomal are not the most computer literate people in the world. I am using a autoexec macro to automate the beginning of the file clean outs that need to be and and several oother thing to, so if there way to automate this to run th the autoexec, procedure, please let me know how. This is the first time I have ever had to archive anything, and I already have a query to list out all of the records which are 2 years old. I also have a deletew query to go with it. What is the next "step"? Thanks alot for the help.

 

[my2Cents]

Hi

I'm a foxpro programmer that is looking at Access and so saw your query.

I think you should look at the hit your employee data is going to have on your database then decide how your going to handle the database or database's.

It's possible to store data on a 100 employees for next 20 years with no problem or significant hit on the database.

Create a dummy Employee record then create 100 of them. Then create as many as would be in one year then 20 years and do some benchmarks with the data, searches, SQl, ect.

You won't be there in 20 years...lol then it's somebody elses problem )

later

P.S. When an employee leaves or is terminated don't delete his employee number (his link to the archived data), create an "Active" field for the employee=.t. or .f.. and the date he was terminated. Also use a 4,5 digit number for an employee so you won't run out of numbers for new employees while your programming there.


Good luck
my2cents

 

[quest4]

Thanks my2cents, many god ideaas, but I am way ahead of you on several of them. I do have one questions left, if I us the link tables will I be able to move the dbases around? I am creating this on my laptop then it will get moved to Accounting for testing, then to the server for full time running. I read a little on the links in help and it did not sound easy to move around. Any thoughts or ideas? Thanks again for the response.

 

[my2Cents]

Hi guy

No, I'm not a Access programmer yet. I'm a DOS Foxpro programmer trying to decide if i should move to Miscrosft Access.

Access programming positions are starting to compete with Foxpro\Visual Foxpro. So I'm lookng to see how difficult a problem it is going to be for me to make the transition or if I should even consider it.

Won't do to ask this question in the "Foxpro" forum )

And as far as archiving, links and the such. I havn't the slighest idea what's happening there. I personally would be looking to store the data in one file or a few files with a relational link.

Later

my2cents