Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: auto-compacting back ends

    My back end, when compacted, it is about an eighth of the original size.

    Is there a way so that the BE is compacted automatically when no users are accessing it? aside from waiting till everyone is done (or no longer) encoding data? should I save the be in mde format? (I have only made mdb files so far).

    Thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't make MDEs for back ends ... ever. Never seen a need for it.

    And nope, there isn't an EASY way to C&R the BE when no-one is using it.

    Wow... too many acronyms

    What you can do though is schedule a C&R for something like 1am by making a small database that, when opened, does the C&R automatically and then use any scheduling software to launch that database.

    Normally, BE files don't bloat out too much to warrant a daily C&R... but monthly is pretty standard.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    Normally, BE files don't bloat out too much to warrant a daily C&R... but monthly is pretty standard.
    Hmm, there's an odd thing here then. A day ago, the backup file of the BE was around 8mb, when I to compacted it, it was only 1.8mb. (i forgot to check the size of the original be at that time)

    The morning backup of the BE today (made around half an hour ago) and the original is only 3mb. So I'm taking into account the possibility that the original BE itself yesterday was 8mb in size.

    While hard drive space isn't a big issue for me, I find it rather odd that the BE file would expand and shrink in no relation to the usage process (we're encoding data into the db every day, so I expected it to grow in size slowly, but not grow that fast, nor suddenly shrink again). What does compacting really do anyway?

    As much as I'd love to attach the two files for comparison, the data contents of the BE are not to be shared.

    Anyway, I guess I'll just stick to manually compacting the db. We have a weekly hdd to disc backup schedule anyway, might as well add that to the list of things to do.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    During normal operation, is there temporary data being added to the BE and then removed? That will do it.

    Is your BE only being used for data? No forms etc?

    C&R essentially defragments the file. If you modify an object, the original is often just de-catalogued... the modified object is appended to the end of the file and the original is just a waste of space. C&R will reclaim that space. Well, actually, that's the compact part... the repair part does something different and doesn't really affect space.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jun 2008
    Posts
    163
    My BE is only tables. All my queries and forms are on the FE.

    I guess the temporary data would be it. Majority of the records gets updated often, and from what you explained, that would mean that the modified objects are creating the swelling.

    Thanks for the info. At least I don't have to worry that some anomaly is happening.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    When all is said and done, a back end is just an ordinary database which can be turned into a front end simply by executing a query. A query executed by a link is simply that. Reading between the lines on some research I had to Do to clear up a problem, I believe that there is quite a lot of caching and buffering going on and regular compacting is useful to reset everything. In databases with big tables or with many relationships (which generate informal indexes) there could be a performance issue if this is not done.

Posting Permissions

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