Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autocompact (Access 2000)

    After the command Application.Quit i have the following code:
    Application.SetOption ("Auto Compact"), 1

    it gives no error, but it seems that it does not diminish the size of the database.
    is my code working and what is the puspose of compacting if it does not diminsh the size?

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

    Re: Autocompact (Access 2000)

    That line should be put in *before* the Quit command. Otherwise, it won't be set because the application will have already terminated.
    Charlotte

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

    Re: Autocompact (Access 2000)

    Charlotte already pointed out that the instruction should be before Application.Quit.

    Apart from that, the instruction Application.SetOption "Auto Compact", True only needs to be executed once for a database, not every time you quit. The instruction does not compact the database, it just sets an option in Tools | Options. From then on, the database will be compacted automatically each time it is closed and no other users are using it.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Autocompact (Access 2000)

    Hello, Hans: Here's an amateur's question for you. If you want an application to automatically compact on close, you go to Tools>Options and check the little box. Why would one go to the trouble of writing code to do the same thing? Just 'cause you can?

    I'm sure there's a good reason

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

    Re: Autocompact (Access 2000)

    Hi Lucas,

    Search me. Our Bulgarian friends (primerov/svetljop/piligrim/pilgrimswake/aral/...) should answer that. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: Autocompact (Access 2000)

    I'll give you one answer. In our applications, we don't leave the option turned on because it isn't necessary and is likely to make some of our users think something is wrong when the Access window doesn't close immediately. We turn the option on under specific circumstances and we have a test in our startup code to turn it back off again.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Autocompact (Access 2000)

    OK. You mention that "it isn't necessary" (to compact the database) every time you close it. I suppose that's quite correct

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

    Re: Autocompact (Access 2000)

    It is a good idea to compact a database regularly. However, Compact on Close will cause a noticeable delay when closing the database, especially if the database is/has become large. If the users know little about Access, they will become worried about this, and pester IT support or the developer with questions about this delay. Also, in an environment where users will open and close the database frequently, you could have the following: at a certain moment, only one user is active; (s)he closes the database, so Access starts to compact it. During this, another user tries to open the database. Either (s)he can't get in, because the database is locked for compacting, or (sh)he manages to get into it and Access can't complete the compact, in which case you'd probably end up with a copy of the database named db1.mdb (for in reality, Access can't compact a database to itself; it creates a compacted copy db1.mdb, then deletes the original, and renames the copy.) In such cases, it might be better to schedule compacting for a time when nobody is using the database, so that the users don't notice it.

  9. #9
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autocompact (Access 2000)

    A question in the same vein. Is it necessary to regularly compact a db that's only used as a front-end - ie. no data is ever entered into tables in this db? Also, I have a db in which I locked the code with a password and I set AllowBypassKey to false. When I had the compact on close option checked, the compact occasionally failed. I would love to know why. I'm wondering if it's the protected code thing, cuz I have another db with unprotected code but allowbypasskey = false and no problem with compact on close.

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

    Re: Autocompact (Access 2000)

    In my experience, front-end databases bloat significantly less than back-end databases, but even they will double or triple in size over time.

    I don't know what exactly causes compact on close to fail. It occasionally happens to me in an unprotected database that only I use.

  11. #11
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autocompact (Access 2000)

    Thanks, Hans. I think I will forget about compacting my front-end db using compact on close because I don't want my users to freak out if the compact fails. I'll just have them make a copy before beginning to use it, and if it gets too big, they can always replace it with this copy. The only time the front-end will change is if I make changes to it in which case I have to send them a new copy anyway.

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autocompact (Access 2000)

    If you truly don't ever update data in the front-end, one option is to make the front-end read-only at the file level. That way it never bloats. We do that for complex front-ends where we download a new version to the workstation when we make changes.
    Wendell

  13. #13
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autocompact (Access 2000)

    Thanks, Wendell. Do you mean that I should mark the mdb file as read-only in the file properties box?

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autocompact (Access 2000)

    Yes - that's the idea. So you have to be sure your users aren't making any updates to the front-end.
    Wendell

Posting Permissions

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