Archiving a database

[golferbabe38]

I have no idea how to even begin an archive. Can anyone give some direction? I've spent two days searching the web and trying to understand the Microsoft website directions along with Access for dummies. The only thing I can find is something about using Products_Appends and Products_Delete queries.

 

[Gethzerion]

If this doesn't help, then please be kind in your response.

My understanding of archiving is as follows.

Firstly create an empty table (if one doesn't exist) that is a duplicate of the table you wish to archive records from. The only important change is that if you have used an autonumber data type then you need to specify this as a long integer in the archive table.

Then identify the records you want to move - say older than 12 months. Using an append query add these records to the new table.

Then using a delete query, delete the records you have moved to the archive table from the existing table.

The above queries can either be built in the QBE, or written in SQL within a VBA module.

If you do an internet search on either an Append or Delete query you can find a number of examples to work from.

What you also need to be very careful of, is not creating orphan records in you "live" database.

 

[golferbabe38]

Thank you very much for the info. I'm assuming it's just the one table that I need to archive. Do I keep that table in the same database? Just name it table archive? or something like that. Sorry for the novice questions.

 

[boblarson]

Thank you very much for the info. I'm assuming it's just the one table that I need to archive. Do I keep that table in the same database? Just name it table archive? or something like that. Sorry for the novice questions.

The answer to your question is dependent upon a couple of other questions.

1. What is it you are needing to Archive?

2. Is it database SIZE you are trying to save on, or some other reason?

 

[golferbabe38]

This is a database that our physicians log onto through the internet to record the meetings they go to and the amount of time they spend there. The database is not very large, but everytime they doctor logs in, they have to scroll through the last few years of entries to get to a blank screen. My manager is asking that I archive the data.

 

[GaryPanic]

Ok

Nice and easy
first make a copy of your d/base

now make a copy of the table
call it archive1

now you have 2 tables with the same info in
delete all the info you do not require from the orginal table
-
I would recommend that you build in a form to view archvies in to your system

g

 

[Zigzag]

Hi Gary,

I think you might have missed a step there, With what you have explained you will end up with some records existing in both tables.
I think it would be better to create "archive1" as described but then delete all the data out of it. Compact and repair to reset Autonumbers.

Then use the solution posted by Gethzerion.

"Then identify the records you want to move - say older than 12 months. Using an append query add these records to the new table.

Then using a delete query, delete the records you have moved to the archive table from the existing table."

Garry

 

[golferbabe38]

Do I make a copy of the table within the new database and not in the original database?
And thank you for simplifying this for me! I really appreciate it, this has been so frustrating

 

[GaryPanic]

keep it within the exisiting D/base

Once you get to a large B/base then you can look at a sepearate Archive D/base (only need to look at this when speed becomes an issue )

Garry..
yeeah oops - I would delete records in both tables - ones i want to archive and ones I want keep current in the relivant tables etc

but from that point forward I would use the append option - but for ease append would be the cleanest option

golferbabe.. when messing about with B/bases always take a copy before you start ( memory stick etc) - that way if it goes pear shpaed - you still have the orginal to hand

 

[GaryPanic]

Your orginal B/base should have 1 extra table in it - called archive _or whatever you have called it ....
this then becomes the master d/base (once you check it and ensured that everything is as per you requirements)


your copy d/base is now redundant as it will not have this extra table ...
I keep my old d/base for a while - but rename them with a date on it spo xxxx280907

 

[boblarson]

Instead of archiving, why not just have the form be Data Entry YES so that the old entries don't show up during entry and then you can have a button to change it to Data Entry NO so that they can view all past entries. Then, there's no archiving necessary.

 

[GaryPanic]

Hey .. Bob - no one likes a smart arse...(LOL)
only kidding

 

[aikea]

I used to work on an ordering system that a company use it to track orders. Because the company process few hundred orders a day, I create two table, one "Past Order" table and one "Recent Order" table.

I first created an append query, the query will open "Recent Order" table, find all records older than 24 hours then add them into "Past Order" table.

Then I created a delete query, the query will find all records older than 24 hours, delete them form "Recent Order" table.

I put this two query into a macro. So, any one can simply run the macro to synchronized these two tables. The "Past Order" contains all records until last 24 hours, the "Recent Order" table contains records made within recent 24 hours.

Because most operations were done on the "Recent Order" table, the normalization was compromised for efficiency.