Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compact With Code (Access 2000/2003)

    I am working on a database that I inherited. Basically it downloads data from an Oracle database, does some reformatting, then posts the data to several other Access databases. I am using Access 2003, but the file format for the database is Access 2000.

    The problem is that it is beginning to fail due to bloat. My solution, I thought , was to run the compaction routine about half way through, then resume the code.

    I set up a way for the database to know if was to start at the beginning or in the middle when the opening form timer fired. That seems to be working fine.

    My problem is that I get an error when it tries to compact after the first section of code.
    The error states:
    Richard

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

    Re: Compact With Code (Access 2000/2003)

    It's probably better to run the parts manually.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact With Code (Access 2000/2003)

    I understand it might be better do run the compact from the menu with the mouse.
    My problem is this db opens and runs based on a scheduler event. This is to help with network traffic and such.
    As it is working now, it runs the fisrt section of code, with is the most download intensive, then when I come in, I stop the code, compact the db, then restart the code.

    I am hoping for a way that would allow the removal of my manual intervention.
    Richard

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

    Re: Compact With Code (Access 2000/2003)

    Not easily. You can turn on Compact on Close in code and you can shut the database down in code, which will trigger the compact. Getting it to restart is the problem. You can't compact the current database in code from the current database. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> The only way I've managed to do it is by opening a second database and using it to restart the first one after it closes and compacts.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact With Code (Access 2000/2003)

    >You can't compact the current database in code from the current database.

    I've had some success using the method linked to by <!post=this post,440607>this post<!/post> by our ubiquitous friend. N.B. in calling it from other than a Switchboard, I've had to preface it with <code>DoCmd.Close acForm, Me.Name</code>

    HTH
    Gre

Posting Permissions

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