Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Archiving old information (2000 SR-1)

    We have a database that is getting fairly large. We'd like to move table information older than a certain date to an archived database. How can we do this?

    Thanks,
    Bob

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Archiving old information (2000 SR-1)

    I'd run a Make Table query using the date as the cut off point. Then I'd change the query type to a delete query to delete the records I'd just exported.

    Need more detail?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Archiving old information (2000 SR-1)

    Be careful doing this. If you are using Autonumber keys, you could wind up reassigning an older number to a new record. Can you give more details on the structure of your database?
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archiving old information (2000 SR-1)

    Good issue, Charlotte.

    This is something I've wanted to do as well but I've been concerned about the autonumber keys being renumbered in the new table. Apart from exporting the autonumber field to a number field, is there anyway to ensure that the autonumber field remains the same?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Archiving old information (2000 SR-1)

    Autonumbers won't change when you append records to another table as long as you append the autonumber as well, and yes, that's legal. The problem is that if you remove any records from the end of a current table (for instance, tables that are related but not indexed on date), a compact of the active database can reset the next autonumber to one higher than the highest number in the table ... which will duplicate a number you archived. In an archive, you could change the datatype to Long instead of autonumber, which wouldn't affect any functionality but might make you feel safer. However, your problem is in the current database, not the archive. If all the related records get moved, it might not be a big problem, but if you have a mixture of lookup values which don't get moved and transaction records that do, you can still wind up with conflicts. They won't show up unless you try to restore archived records or you create a union query on the archive and the active database, at which point things will blow up in your face.
    Charlotte

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Archiving old information (2000 SR-1)

    So, the sequence of events might look something like this?
    1) Run Make Table query using the date as the cut off point.
    2) Run a delete query to delete the records I'd just exported.
    3) Create a new dummy record to hold the last autonumber.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archiving old information (2000 SR-1)

    Thanks--That gets me pointed in the right direction. Right now, I'm leaning towards a macro that runs an append query, then a delete query. I'd also like to prompt for a date cutoff--would that best be done with a parameter query or a dialog box?

    Also, yes, there are auto-numbers, and they're used to relate tables, so I'll have to be careful with them.

    Bob

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archiving old information (2000 SR-1)

    lol yes, Charlotte, I have some experience with Access powder burns! I'm also aware of the removing a record and then compacting issue which is what I was concerned about.
    Thanks again for the info, makes it all much clearer.

Posting Permissions

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