Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87

    Unanswered: Archiving old data

    Hi,

    I'm trying to Archive old records from two tables in my current database.

    I know I need to create a make-table query to send the data, but first I want to create a new file so the archives can be distinguished between.
    then once records have been archived I want to delete the records that have been archived.

    I want to run it all from a form, using the following criteria:
    tblcase.dateadded <[form]![frmarchive]![txttxtdateadded]

    Both tables have a caseID as primary key.

    Any suggestions?

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    I assume you are trying to archive by date. If so, you can use the dates on your form as the criteria for the query. Something like Between Forms![FormName]![txtStartDate] and Forms![FormName]![txtEndDate] You will need two text boxes on your archive form and a command button to run the query. The delete is just a delete query with the same criteria.

  3. #3
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    I would like the database to do it all, once the user clicks OK

    so:

    1. Create new archive database (using date in name i.e. bert26012004.mdb)
    2. Run make table query 1
    3. Run make table query 2
    4. Run delete query (can be done for both tables).

    The archive will be processed either at the end of every month or every quarter and will archive all data prior over three-six months old (although user will key date)

  4. #4
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Don't think you can create a new db on the fly. I suggest you create a new db with the archive tables in it. I archive this way each month and yearly.

  5. #5
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    oh, right just the database, I've been rebuilding (because source not available) does this, there are two make table & a delete query but where's it making the new one?

  6. #6
    Join Date
    Jan 2004
    Posts
    16

    perhaps solution!!!

    sorry for my bad english
    perhaps the solutionfor you
    Attached Files Attached Files

  7. #7
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87

    sorted

    I've decide that instead of creating a new database I'd link tables from an the archive database and use an append query to transfer the data.

Posting Permissions

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