Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    156

    Unanswered: Compacting and Back-ends

    Good day, all!

    I have a quick question. Does the "Compact on Close" feature in Advanced options compact just the front end database if the database is split, or does it compact both the front end AND the back end, which is the one that matters? Thanks!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    I dont know if it is the same but do you mean the compact and repair? I think it depends which one you run it in. Whether you run it in the MDB or the MDE. Im sure someone will correct me if im wrong here?

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    As far as I know, "Compact on Close" (which I do not use!) applies only to the specific MDB not to any attached ones.

    IMHO, this option was added at the behest of the marketing department. An Access database does not need to be "compacted," let alone every time it is closed.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Sep 2003
    Posts
    228
    Quote Originally Posted by sundialsvcs
    As far as I know, "Compact on Close" (which I do not use!) applies only to the specific MDB not to any attached ones.

    IMHO, this option was added at the behest of the marketing department. An Access database does not need to be "compacted," let alone every time it is closed.
    You don't notice the file size of an access database steadily increase if it is not compacted?

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The one that matters is the back end. As you add or delete data from you tables the indexes keep the data in order, but the data could be all over the hard drive, similar to fragmented files. When you compact a database all of the objects in a database are written to a new database and tables are written in index order. Keeping the records of one table all together. I have seen this significantly increase performance. But if you have a small database you probably won't notice anything.

    Another issue you might have to consider is, how long will the compact take? With large databases it could be a half hour or so to compact.

    My experience has been, only compact when you have space issues on your computer or you are experiencing performance problems.

  6. #6
    Join Date
    Sep 2003
    Posts
    228
    Quote Originally Posted by sundialsvcs
    As far as I know, "Compact on Close" (which I do not use!) applies only to the specific MDB not to any attached ones.

    IMHO, this option was added at the behest of the marketing department. An Access database does not need to be "compacted," let alone every time it is closed.
    You don't notice the file size of an access database steadily increase if it is not compacted?

  7. #7
    Join Date
    Oct 2003
    Posts
    706
    Quote Originally Posted by ottomatic
    You don't notice the file size of an access database steadily increase if it is not compacted?
    The MDB will increase steadily in size, up to a point. Then it will stop.

    Space in a database is allocated in "pages." In the MS-Access system, pages contain a varying number of records according to how large the field-values actually are. As records are inserted and removed, the content of the pages will change. When a new record is inserted, it may be added to a previously allocated page that isn't full, or a new page may be added.

    When you compact a database, all of the pages are full. There is "no room in the inn." So as new pages are inserted, a lot of "page splitting" will occur and page-allocation will grow quite rapidly; giving apparent (but only short-term) credence to what you observe.

    As time goes on, and once again depending very much on the actual distribution of data-values being inserted and removed, it will become more and more likely that a new page doesn't have to be allocated to make room for a new record. The file does not grow.

    I support a database that was brought on-line in 1999 and has not been repaired or compacted once since then. Runs fine.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    I always compact my back /fornt database

    if keep then small

    The way I compact the Backend click the compact on close then .
    write a Autoexec Macro that Closes the database then opened
    then get the sever to run it once a day about 2:00am in the moring
    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.

  9. #9
    Join Date
    Jul 2004
    Posts
    156
    Thanks for all your answers!

    Sundial, you seem to have the proof in the proverbial pudding with your db from 1999. Your explanation makes sense from what (little) I know about how data is stored in Access/Jet. But, you seem to know more about the inner workings of Access db's, so I'll have to take your word for it.

    DCKunkle, you seem to have some good points, however, about the fragmentation. Does anyone have any comments about this?

    Just today I was placing a split database on a server for the first time. I tried running/editing the front-end from the server first off before I downloaded it to the workstations it's going on. I got an error twice out of just a few times running the database and its compact on close while it was on the server. Something to do with it being read-only so it saves it as db1.mdb. The only problem with this is that it didn't leave the originally named database in the folder. I only got this when it was on the server. And, yes, the user has access rights to the folder. Like I said, it worked most times, just not the two times before I took it off the server. Does anyone have any explanations for this and how to prevent it in the future in case it happens with the back-end?
    DocX

    The teachings of God's Begotten: 2 John 1:9

  10. #10
    Join Date
    Jul 2004
    Posts
    156
    Sundial, by the way, do you know of an approximate size/time when the database starts growing only at the pace of data entry size?

    Also, please read my above post and see what ya think. Thanks all!
    DocX

    The teachings of God's Begotten: 2 John 1:9

Posting Permissions

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