Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: how can someone create an archive?

    Hey guys,

    Is it possible to create an archive for a database. I anticipate that the database i have created will be quite large over a small amount of time, i want to be able to archive tables. can this be done?

    when a record is several months old can it be archived.


    Thanks

    Aboo

  2. #2
    Join Date
    Sep 2006
    Posts
    265
    Unless the quantity of records is enormous i.e. it impacts on the performance of your database, I would simply flag each record C for Current or H for History.

    Simon

  3. #3
    Join Date
    Dec 2004
    Location
    Connecticut
    Posts
    85
    Here's the simplified version:

    If your database is split, simply create a duplicate of the back-end database with just the empty tables.

    Then create an administrative front-end with links to the original back-end and the new back-end. You'll want to use aliases to keep the live and archive tables straight. You'll also want to adjust record locking so that nobody can edit a record while you're trying to move it.

    Then create the queries to select the data you want to archive, one table at a time. Add those records to the matching archive table and then delete the same records from the live tables. Be sure to add error handling. If you're really anal, your error handling will undo all of the moves if something tragic should happen while you're in the middle of moving records.

    If your database isn't split, copy all of the table structures to a new database and develop an administrative front-end as above.

    You can also develop a mechanism to restore records from the archive if they are needed.

    There are threads here that cover most of these suggestions.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is a quick and dirty (but rather safe) example of what can be done.

    The main sub ArchiveTable takes 3 parameters:
    ArchiveDBName is the name of the mdb where you want to archive a table,
    TableName is the name of the table you want to archive,
    KeepDate is the date of the records you want to archive up to.

    This implies that the table must contain a column CreationDate that will be used to determine which records you want to archive. You can easily adapt the code to archive according to another criteria. You should also implement a true error handler: there is just a skeleton of it in the example.

    As the code is a bit long to be pasted here you will find it in the attached file. Open the Archive_Form form to see how it works.

    Have a nice day!
    Attached Files Attached Files

  5. #5
    Join Date
    May 2006
    Posts
    178
    hi,

    will this work if there are linked records?

    Thanks

    aboo

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No, in the case of an attached table it will copy the link to the attached table into the archive database.

    In such case the code for CreateTmpArchiveTable must be changed and instead of using DoCmd.CopyObject to make a copy of the table you want to archive you have to create a local temporary table with the same structure as the one of the attached table then import the records you want to archive into this temporary table before exporting it to the archive database.

    You can also include the original code into the mdb actually containing the tables (the back end).

    Have a nice day!

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sinndho,

    I'd like to put your ArchiveDatabase example in the code bank. Would you mind adding comments to the code and then post it to the code bank or send it to me?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Hi pkstormy!

    Sure, no problem. I'll comment it and repost it soon.

    Have a nice day!

  9. #9
    Join Date
    May 2006
    Posts
    178
    sinndho,

    im finding it hard to follow, i look forward to looking at your commented code, is it possible for you to incorporate a linked tables example, so i can use it as a guide as i have about 6 linked tables.


    Thanks

    aboo.


    Is this the only, or the best method to do this?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Hi pkstormy and aboo!

    Here you are: this new version can archive records from a local table and records from an Access linked table.

    pkstormy: I have commented the code the best I could, i hope it's clear enough; feel free to modify it as you see fit.

    Everyone: Please keep in mind that this program is just a skeleton (if I may say so!) and several important features are missing:
    First, an error handling mechanism should be implemented. The structures to detect errors are in place but these are not handled properly.
    Second, only a few cases among many other possible situations that could be met in a production environment are handled.

    If you want to test the program using the form Archive_Form, the file ArchiveDatabase.mdb can be anywhere on the computer
    however the second file Sales.mdb that contains a table that is attached in ArchiveDatabase.mdb must be located in the
    C:\Data folder (or you have to change the attachment). As it is the archive database Sales2008.mdb will be created in the
    C:\Archives folder but this can easily be changed.

    Have a nice day!
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •