Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2006
    Posts
    35

    Unanswered: Compact and Repair

    I tried to search on this but it timed out (am I the only one experiencing the annoying search-window-hidden-beneath-the-banner-ad-syndrome?) so I apologize if there is a similar post....

    I have a split, multiuser database and would like to have a safe method of compacting and repairing the database built in. I have disabled the menu options which has also taken out the TOOLS | COMPACT & REPAIR DATABASE. I attempted to create a simple macro using two lines of RUNCOMMAND (COMPACT then REPAIRDATABASE) resulting in an error message that I cannot do this from a macro. I still want to limit menu options. I considered the COMPACT on Exit option, but with multi users thought it might be problematic.

    Any thoughts how best I might keep the connections clean?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You spotted the problem: You can't initiate a C & R while in the DB to be C & R'd ...Do you have a stub app that you could launch the main from? You could put the C & R option in there ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    If it is the backend I Setup the Database to C & R on close

    Then I write the Macro

    Call Autoexec and it Quits the Database

    Then about 1:00 morning I get the Scheduled to open the database

    Job done
    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.

  4. #4
    Join Date
    Feb 2004
    Posts
    90
    If the database is split, there shouldn't be a problem on using the "compact on exit" on the front ends, as these are all independent of each other, so there isn't any locking issues.

    However, this won't compact your backend. To do that I would follow myle's advice and set up a schedule on the server that the backend is stored.

    The way I do it is set a schedule on the server to open the backend database at midnight. It is set to "compact on exit". It has a form that is set to open on startup, and in its timerinterval event after 5 seconds is the commad to quit the database. This means that if I need to open the back end I've got 5 seconds to close the from and stop the database from quiting.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Just curious: do you need to compact the front ends? I wouldn't think so, I'm just checking.

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Try this....somewhere in your DB.

    Application.CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities"). _
    Controls("Compact and repair database...").accDoDefaultAction


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by tcace
    Just curious: do you need to compact the front ends? I wouldn't think so, I'm just checking.
    Very likely yes, but I suppose it may depend on the app. I've got a taxi reservation/dispatch app where the front end is about 7mb. In use, because of the constant querying, I've found it to have grown to over 100mb in a couple of weeks. Performance began to degrade noticeably. I set it to compact on close. I've got others that don't grow so dramatically, so it certainly depends on the app.
    Paul

  8. #8
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    I've had problems with the database corrupting or disappearing altogether while compacting, so I scheduled a weekly maintenance time during the day, and the users had to log out. Then, I'd copy the database to my hard drive and THEN manually compact the copy, then replace the server copy. This database had serious bloat, from about 400MB - 1GB in a week's time, and frequently had 50+ concurrent users, so I think that made it more fragile. Not to mention it could take 20 minutes to compact on the server.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    In summary:
    1) Having a database compact itself is tricky and not a good idea.
    2) Compacting the live copy is a bad idea.
    3) Compacting on the network is likely to fail (the user problem), and not work very well otherwise.

    My thoughts:
    1) Have a seperate process run the compact - use the windows scheduler to trigger it and have it copy to local, compact, verify success and then copy back (rename the old version). Renaming the old version makes it very quick and easy for the IT folks to restore should the compact corrupted the DB and it went unnoticed until the next user logge din.
    2) Never compact the live version - even on your desktop while you are working on it. You always want to have a backup.

    Unless there is a lot of data being deleted in the BE, a completed DB app that is running in "steady state" should not require compact very often. If it does, something in the BE is probably not setup right.

    As for the FE, since the FE isn't supposed to store data anyway, it shouldn't need to be compacted. If it bloats for some reason, either fix the reason or simply replace routinely instead of compacting. Providing the FE is run locally (which it should be) then chances are a bit of bloat won't hurt you anyway.

    tc

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tcace
    As for the FE, since the FE isn't supposed to store data anyway, it shouldn't need to be compacted. If it bloats for some reason, either fix the reason or simply replace routinely instead of compacting. Providing the FE is run locally (which it should be) then chances are a bit of bloat won't hurt you anyway.

    tc
    Not necessarily true ... Running all those queries will bloat the FE ... I do agree that compacting or replacing periodically is a good idea. I've found that with moderate to heavy use, an access FE will last about 2-3 months lighter use: 3-6 months. This all depends of course on the apps in the DB and how they were written ... But, no matter how, Access is still a leaky boat and a bloated pig that doesn't clean up after itself (codewise that is) ...
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    Quote Originally Posted by LisaChow
    I've had problems with the database corrupting or disappearing altogether while compacting, so I scheduled a weekly maintenance time during the day, and the users had to log out. Then, I'd copy the database to my hard drive and THEN manually compact the copy, then replace the server copy. This database had serious bloat, from about 400MB - 1GB in a week's time, and frequently had 50+ concurrent users, so I think that made it more fragile. Not to mention it could take 20 minutes to compact on the server.
    How Meny Memo feild do have in the database Do you need them

    2 meny memo feild Have blotted my databases and slow it down heaps.
    Last edited by myle; 08-24-06 at 22:39.
    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.

  12. #12
    Join Date
    Jan 2006
    Posts
    35

    Thanks!

    Thanks to all for your comments. I didnt fully normalize this database intentionally so that someone could pull it into a spreadsheet in a pinch but that shouldnt account for the poor performance which happens even when I am the only user in the database. I have certainly been disappointed by the performance: only three users at one time might be in it at the same time and it only contains about 500 records after four months activity. (My old Clipper-compiled programs would ROCK in this situation!) I am also disappointed by the lack of basic supplied utilities for what is not a complicated database. I was hoping this little project would be a create-then move on-type of project.

    Sounds like an off-hour separate process is what I am looking for here. I will have to get with IT and plead my case and hopefully they will "allow" this to be run on "their" server. Thanks again!

Posting Permissions

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