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

    How to Compact Programmatically (Access 2000 /2002)

    I would like to be able to compact my mdb file programmatically. Here is the reason: The database is shared by two machines (both running Win 2K Pro) and is located on one machine's C drive. I have drive R: mapped to the folder in which the mdb file is located on both machines. All VBA references are to drive R: where a path is necessary. This works well. What doesn't work is attempting to compact the mdb file on close. It hangs up midway through the process. I've done some searching on Win 2K forums and have found some hang up issues relating to redirected drives. If I open the file as being on drive C: (on the one machine where it is located, of course), it compacts perfectly.

    My thinking is to develop some VBA code that would compact the mdb file as being located on C:, but only for the computer name on which it is located. The other computer would not be able to execute that code.

    I fear that I have not made my problem very clear but I am basically looking for some VBA code that I can insert that will allow me to compact the mdb file in which it is located. Any help will be greatly appreciated.
    Carol W.

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

    Re: How to Compact Programmatically (Access 2000 /2002)

    You'll have to rely on code that executes when a last form (like a switchboard or a hidden forms) is closed. You could test for the existence of the file in the local drive and set the "auto compact" option on or off using SetOption. That will allow you to turn on the compact on close option if the database is local and turn it off if it isn't. If it is turned on, the database will compact when it closes.

    Of course, if both users are in the database at the same time, the last one who closes wins, so it may not get compacted. Access wasn't really designed for this kind of sharing over redirected drives and it has never worked very well in that kind of situation.
    Charlotte

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

    Re: How to Compact Programmatically (Access 2000 /2002)

    Thanks for the reply. I will give this solution a try.

    Can you point me in the direction of any specific issues that exist relative to sharing an mdb over redirected drives? I'm interested. 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
  •