Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Compacting in VBA

    I have an Access front-end file linked to four Access back-end files. All is well, except that the nature of the procedures being run can result in size blow-outs that affect performance.
    • Using Access 2010, although the files are .mdb files
    • Access is being used almost exclusively to run complex procedures on a range of data
      • there is very little in the way of forms or reports
      • data are imported, processed through some rather complicated steps and exported
    • Don't want to use Compact on Close because
      • it only affects the front-end file
      • it requires the user to exit and reopen the database between major tasks
      • it slows things down at the end of the session just when it serves no purpose (till the next session)
    • Therefore I wish to run a custom utility at key points in the processing to compact all five relevant files
      • user doesn't have to worry about remembering to do any of it
      • want to run front-end and back-end compacting (in sequence, of course) at the one time
    • Can successfully run Application.CompactRepair(...) for all the linked databases (having ensured there are no open links)
    • But ... this doesn't work on the front-end (open) database
      • that is no great surprise - Microsoft Help clearly states that will be the case
    • Of course, I can manually compact an open database
      • don't want the user to have to do this
      • the programmatic equivalent in older versions of Access was to use CommandBars...
      • the best I can find on Googling the problem is CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("Compact and repair database...").accDoDefaultAction
    • But ... this doesn't work with the Ribbon
      • have been unable to find a 2010-equivalent that works
      • don't seem to be able to drill down to the same depth
      • for example, using Application.CommandBars("Ribbon").Controls(1).Capt ion just returns "Ribbon", which seems exceedingly useless
    The Microsoft Help process has become so bad I could weep. This one has had me going round in circles all day, and the most useful information I have gleaned has been outside any Microsoft-controlled forum. However, nobody seems to have asked (or at any rate, answered) the specific questions I have. There seems to be general confusion between using CompactRepair and CommandBars, with many posters not understanding when you can and can't compact the file from which the code is running.

    Or, of course, I'm the one who really doesn't understand what is happening. Probably that.

    Any ideas?

    Thanks and regards

    Alison C

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This is a difficult subject as you have found, and I have never found a satisfactory solution (but it is a while since I last looked at this, and in effect gave up).
    I will be interested to see what other responses you get.
    As I see it you can't compact the backends while the front end is open, and you can't run code from the front end unless it is open.

    What about having a utility database with no tables that simply compacts the others? Have a command in the main db that opens the utility and closes itself.
    When the utility opens, it compacts the other 5, then closes itself and reopens the original.

    This assumes there is only 1 user at a time of course.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Hi John -

    Thanks once again for your prompt response, and sorry not to reply earlier. I only see this client once a week, so it wasn't lack of interest that took me so long to get back onto it - just distractions in other client directions!

    Fortunately, this is a single-user application, so at least I don't have to worry about getting a number of users out before compacting.

    I have been able to compact the back-end files, as long as the front end doesn't have any objects open that are using any of the linked tables - easy enough to scan for programmatically, although in this case I know from the nature of the overall application which files will be open so I can specifically close them at the start of the procedure, and re-open them when it's done.

    From my research, I believe that the CommandBars route was the way to compact the active file in earlier versions, and figure that if I can find an Access 2010 equivalent of CommandBars it might solve my problem. If I get there, I'll certainly post the procedure - with 72 views and only your response so far, it looks as though some others might be interested!

    Meanwhile, I have already successfully compacted all the project's files (front- and back-end) using a more sophisticated external database utility. This client has a number of projects all based on the same principle of importing, processing and exporting data, so I created an independent database from which he could chose any of the projects and compact all the files associated with it. Your suggestion of ducking out to a similar utility and then back to the original one may well be the solution - at least until I can find the 2010 CommandBars equivalent!

    Cheers

    Alison C

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You could by just buy a copy of Total Visual Agent .
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Thanks, I didn't know about this product, and reckon it's worth a look.

    Unfortunately, at first glance, it doesn't look like the right product for this particular client, because in some cases he needs to compact more than once during the day - ie between two runs of the routine; and he also plans to run other routines overnight, so setting it to run at the end of his day might be a nuisance, as he needs some manual intervention (selecting his own choice of parameters etc) at the start of the overnight run and would thus have to wait until the scheduled maintenance was done.

    However, I can think of other clients for whom this might be useful, so will take a closer look at it.

    Thanks and regards

    Alison

Posting Permissions

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