Link to home
Start Free TrialLog in
Avatar of toalexsandr
toalexsandrFlag for United States of America

asked on

Archiving Access table older than 6 months

With a push of a button, I need to archive access data that is older than current 6 months based on the date the job was completed. How can I do that in VBA?

Thanks for your help.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If, by "archive", you mean remove it from the current backend, my first question would be "why".  Generally, Access databases rarely grow large enough in a six month period that you would need to actually physically remove the records from the current database into some form of "archive".  Additionally, you would normally want the ability to look backwards and generate reports for at least the previous year, generally longer, and this becomes more difficult if you physically remove those records from the database.

I generally use a technique similar to what is mentioned by Rey (above) when I want to mark records as archived (maybe to eliminate those from some queries).  But if you really want to move them into an external database you could create another database with a table identical to your current table.  Then you would do something like:

strSQL = "INSERT INTO yourTable (field1, fiel2, field3) " _
             & "IN 'C:\archiveFolder\archivedb.accdb' " _
             & "SELECT Field1, Field2, Field3 FROM yourTable WHERE [dateField] < DateAdd('m', -6, Date())"
currentdb.execute strsql, dbfailonerror

Then you would need to either mark those same records as archived (as above), or remove them from you current database with a delete query.  But you also need to consider referential integrity, primary keys, foreign keys, and that type of thing, both in the current BE and in the archive BE.  For example if "yourTable" conatins an autonumber PK in the production BE, then the archive BE should not be a autonumber, but a Long Integer.  There is a lot more to "archiving" records from the current db than simply moving a couple of records, at least there should be.

HTH
Dale
Avatar of toalexsandr

ASKER

I was considering moving them to a different directory, but creating an Archive column and marking it there, sounds much better. I have 2 years of information in the inspection table and we are always working on the last month, and I wanted to eliminate unnecessary query search.
As long as you have an Index on your data column, selecting records should be fast enough.
You can even define a query qryActiveRecords which might look like:
Select  * from MyTable where DateField>Date()-180

Open in new window


Then you can base your other queries off of date to only select what you would consider "Live" or "Active" data. This way, we don't even have to bother with archiving the data.
<but creating an Archive column and marking it there, sounds much better. > indeed
Don't forget that adding an Archived column after the fact is more work than actually deleting the records.  You will need to modify virtually every query that references the table to either include or exclude archived records depending on how the query is used.

I use both methods but it depends on the situation.  Typically when I archive data, I copy it from the primary database and append it to identical tables in the archive with the addition of an additional column that identifies a set of records.  That way if they need to pull something out of the archive and bring it back into the active database, they can use the "set" column.  Dates are nice but you need them in the parent tables and the child tables but pulling back by date is significantly more difficult than by "set".
Pat, can you explain how they use "set" column?
Every table in the archive has one additional column.  You can name it "set" or "batch" or whatever makes sense to you.  That way, you can identify which set of records got archived at the same time.  This is only needed if you want to be able to undo a bulk archive command.  Generally if you would unarchive only one "master" record at a time, the set/batch isn't needed.

When you run the unarchive process, you need to do it in hierarchical order.  For example, if you are archiving Orders, you would unarchive the Order header(s) and then with a second query unarchive the Order details.  If you are doing one at a time, you would use the OrderID to control the append back to the active tables and the delete from the archive.  If you are using a bulk undo, then you would use the set/batch to control the append and delete.
I appreciate everyone's assistance and suggestion but this solution worked better for me. Thank you.

DoCmd.RunSQL "UPDATE tb_Inspections SET Archive=1 where Archive=0 and Datediff('d', [Actual Finish], Date()) >=180"