Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Archiving Data (Access 2000)

    I am developing an archival feature for an app that has been in production for several months. The archival will move records older than 60 days to a set of linked tables. I am developing code that will kick off the archival process on the first Monday of each month. After that, the file will be compressed manually (not via code).

    My question is as follows: If I do not execute this code on days other than the first Monday of the month, will the history tables be brought into memory? In other words, when does Access load some or all of a table into memory? The first time it is accessed? When the mdb is opened?

    Another question: Should I make the code that archives the data a Public Sub? Would that make a difference?

    The main tables are getting large and performance is, at times, slowing down. I don't want to get into a situation where the historical data (which is not needed except for future ad hoc reporting) will be taking up unnecessary memory.

    TIA.
    Carol W.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Archiving Data (Access 2000)

    Are the history tables linked in the standard sense, i.e. the physical tables reside in another database, with a link in the production database? If so, they won't be opened unless the production database connects to them.

    I don't understand your question about making a procedure Public. Public / Private has to do with the scope of the procedure: if it is Private, you can only call it within the module it resides in, if it is Public you can call it anywhere in the database.

    If the number of records is so large that performance is becoming a problem, check whether you have indexes in the right places. Having no index on a table can slow down things, but having indexes on all fields too. If your database is becoming really large and slow, consider moving the data to SQL Server.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archiving Data (Access 2000)

    Thanks for the quick reply.

    Yes, the history tables are linked tables - ie they reside in another mdb.

    By "not opened unless the production database connects to them", do you mean that unless code is executed that refers to those tables they will not be opened?

    I only asked about public versus private subs because I thought that perhaps a reference, in and of itself, to a linked table might trigger opening it without any code actually executing. From your reply, I suspect that is not the case.

    Thanks, again.
    Carol W.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Archiving Data (Access 2000)

    The production database will connect to a table in the history database if you open a table or report based on it (or open the table itself or a query based on it), and also when you open a recordset based on it in code. Just opening the production database does not imply that the history database is opened.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archiving Data (Access 2000)

    That's what I wanted to hear!

    Thanks.
    Carol W.

Posting Permissions

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