Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Question Unanswered: auto compact database

    hi All

    I have a database that holds about 24000 records in a single table.
    at the beginning of each day, these are deleted and another 24000 records are uploaded.
    As you can imagine the size of the database keeps getting bigger enev though the amaount of data never changes.

    is there a way the I can run code or a macro to automatically compact the db everyime it starts?

    thanks in advance
    ActionAnt

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What about looking at the Compact On Close option ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    where is that?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by actionant
    where is that?
    Well if you had searched the Access Help ...

    Compact and repair an Access file
    To ensure optimal performance, you should compact and repair your Microsoft Access files on a regular basis. Also, if a serious problem occurs while you are working in an Access file and Access attempts to recover it, you might receive a message that the repair operation was cancelled and that you should compact and repair the file.

    You must have Open/Run and Open Exclusive permissions for an Access database in order to compact and repair it.

    Compact and repair the current Access file

    If you are compacting a shared Microsoft Access database that is located on a server or shared folder, make sure that no one else has it open.
    On the Tools menu, point to Database Utilities, and then click Compact and Repair Database.
    Compact and repair an Access file that is not open

    Close the current Microsoft Access file. If you are compacting a shared Access database that is located on a server or shared folder, make sure no one else has it open.
    On the Tools menu, point to Database Utilities, and then click Compact and Repair Database.
    In the Database to Compact From dialog box, specify the Access file you want to compact, and then click Compact.
    In the Compact Database Into dialog box, specify a name, drive, and folder for the compacted Access file.
    Click Save.
    If you use the same name, drive, and folder, and the Access database or Access project is compacted successfully, Microsoft Access replaces the original file with the compacted version.

    Compact and repair an Access file automatically every time you close it

    Compacting does not occur if you close a shared Access database while another user has it open.

    Open the Access database or Access project that you want Microsoft Access to compact automatically.
    On the Tools menu, click Options.
    Click the General tab.
    Select the Compact on Close check box.
    Note You can stop the compact and repair process by pressing CTRL+BREAK or ESC.
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Mar 2004
    Location
    Oakland, CA, USA
    Posts
    26
    Hello

    I just want to add that if you use Access 97, Compact on Close check box wasn't there yet.

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Under ACCE$$97 Look up
    DBEngine.CompactDatabase

  7. #7
    Join Date
    Feb 2011
    Posts
    1

    May 2011 -no answer for a Auto Compact and Repair yet

    There should be a way to run an "auto-marco" as in Excel that does the Compact and Repair that can be put on an XP-Scheduler to run nightly.

    If anyone has this process working let me know.

    Thx

    Russ

    Russell.Neuman @ LFG.com

    I will post one if I figure it out.

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what I do is set my msaccess to compact on close then in the BE one I create a macro call autoexe all that does is quit the database then I use Scheduled Task to open it at 1am in morning the autoexec kick in and closes it and the compact is done

    also putting the autoexec in you can't open it that also keep the noseie user out LOL (well you can by holding down the shift key)
    Last edited by myle; 05-25-11 at 17:43. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Unless this is a single user db I wouldn't encourage the use of Compact and Repair on a shared Access MDB.

    as others have said by all means do it as a batch process, when you knwo there is no one connected to the db
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And back up the file before doing it! C & R has long been cited as a frequent cause of corruption, although in this scenario it may not be as much of a problem as in most.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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